Reverse Engineer Foreign Keys from system tables

[VolkerBarth]

I am working on understanding the SQLA 16/17 system tables and I'm a bit stuck on where to find index FOR OLAP WORKLOAD.

I was able to find a reference to SYSPHYSIDX max_key_distance but am unable to figure this out.

Here is what I have so far if anyone is interested

SELECT ForeignKeys.foreign_table_id                             AS TableId,
       SQL.SmartQuote( ForeignTables.table_name )               AS FTableName,
       SQL.SmartQuote( UPPER( ForeignOwners.user_name ) )       AS FTableOwner,

       SQL.SmartQuote( PrimaryTables.table_name )               AS PTableName,
       SQL.SmartQuote( UPPER( PrimaryOwners.user_name ) )       AS PTableOwner,

       SQL.FKIndexColumns( 
         ForeignKeys.foreign_table_id, 
         ForeignKeys.foreign_index_id,
         1 )                                                    AS FIndexList,
       SQL.FKIndexColumns( 
         ForeignKeys.primary_table_id, 
         ForeignKeys.primary_index_id,
         0 )                                                    AS PIndexList,
       IF ForeignTables.clustered_index_id =
         ForeignKeys.foreign_index_id THEN 'CLUSTERED'
         ELSE NULL
       ENDIF                                                    AS ClusteredIndex,
       CASE ForeignKeys.check_on_commit
         WHEN 'Y' THEN 'CHECK ON COMMIT'
         ELSE NULL
       END                                                      AS CheckOnCommit,

       CASE ForeignKeys.nulls
         WHEN 'N' THEN 'NOT NULL'
         ELSE NULL
       END                                                      AS AllowNull,
       CASE ForeignKeys.match_type
         WHEN 1 THEN 'MATCH SIMPLE'
         WHEN 2 THEN 'MATCH FULL'
         WHEN 1 THEN 'MATCH UNIQUE SIMPLE'
         WHEN 1 THEN 'MATCH UNIQUE FULL'
         ELSE NULL
       END                                                      AS MatchType,
       CASE FKDeleteAction.referential_action
         WHEN 'C' THEN 'CASCADE'
         WHEN 'D' THEN 'DELETE'
         WHEN 'N' THEN 'SET NULL'
         WHEN 'R' THEN 'RESTRICT'
         ELSE 'RESTRICT'
       END                                                      AS ONDeleteAction,
       CASE FKUpdateAction.referential_action
         WHEN 'C' THEN 'CASCADE'
         WHEN 'D' THEN 'DELETE'
         WHEN 'N' THEN 'SET NULL'
         WHEN 'R' THEN 'RESTRICT'
         ELSE 'RESTRICT'
       END                                                      AS ONUpdateAction,
       FTConstraint.constraint_name                             AS ConstraintName,

       SQL.ForeignKey( FTableOwner,
         FTableName,
         AllowNull,
         ConstraintName,
         FIndexList,
         PTableOwner,
         PTableName,
         PIndexList,
         MatchType,
         CheckOnCommit,
         ClusteredIndex,
         ONDeleteAction,
         ONUpdateAction)                                        AS SQL


  FROM SYS.SysFKey        AS ForeignKeys
  JOIN SYS.SysIdx         AS FKIndex        ON ( ForeignKeys.foreign_table_id = FKIndex.table_id AND ForeignKeys.foreign_index_id = FKIndex.index_id )
  LEFT OUTER
  JOIN SYS.SysTrigger     AS FKDeleteAction ON ( FKIndex.table_id = FKDeleteAction.foreign_table_id AND FKIndex.index_id = FKDeleteAction.foreign_key_id AND FKDeleteAction.event = 'D')
  LEFT OUTER
  JOIN SYS.SysTrigger     AS FKUpdateAction ON ( FKIndex.table_id = FKUpdateAction.foreign_table_id AND FKIndex.index_id = FKUpdateAction.foreign_key_id AND FKUpdateAction.event = 'C')
  JOIN SYS.SysTab         AS ForeignTables  ON ( ForeignKeys.foreign_table_id = ForeignTables.table_Id )
  JOIN SYS.SysConstraint  AS FTConstraint   ON ( ForeignTables.object_id = FTConstraint.table_object_id AND FTConstraint.constraint_type = 'F' AND FKIndex.object_id = FTConstraint.ref_object_id )
  JOIN SYS.SysUser        AS ForeignOwners  ON ( ForeignTables.creator = ForeignOwners.user_id )
  JOIN SYS.SysTab         AS PrimaryTables  ON ( ForeignKeys.primary_table_id = PrimaryTables.table_Id )
  JOIN SYS.SysUser        AS PrimaryOwners  ON ( PrimaryTables.creator = PrimaryOwners.user_id )

[VolkerBarth]

I don't know - but thanks for the pointer to the FOR OLAP WORKLOAD clause, which I seem to have overlooked so far...

[Chris26]

I don't know either. Just wanted to point out that the case statement for MatchType contains duplicate when values.

   CASE ForeignKeys.match_type
     WHEN 1 THEN 'MATCH SIMPLE'
     WHEN 2 THEN 'MATCH FULL'
     WHEN 1 THEN 'MATCH UNIQUE SIMPLE'
     WHEN 1 THEN 'MATCH UNIQUE FULL'
     ELSE NULL
   END

Should be

   CASE ForeignKeys.match_type
     WHEN 1 THEN 'MATCH SIMPLE'
     WHEN 2 THEN 'MATCH FULL'
     WHEN 129 THEN 'MATCH UNIQUE SIMPLE'
     WHEN 130 THEN 'MATCH UNIQUE FULL'
     ELSE NULL
   END

[JimDiaz]

Thanks very much

[VolkerBarth]

Have you had a look at the sa_get_table_definition system procedure? That might also do the trick for you. (Note: I have not used that myself.)

Aside: This seems to be a system function, not a procedure. 🙂

---

FWIW, this seems to do a job comparable to the DBUNLOAD utility, i.e. the FK definitions are not part of the CREATE TABLE statement but are added with further ALTER TABLE statements for each FK.

[JimDiaz]

Wow didn't know it existed thanks!