Reverse Engineer Foreign Keys from system tables

[JimDiaz]

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!

[MarkCulp]

Dbunuload -n will generate all of the DB schema including so is the foreign keys. You could use that and then pull out the text from the sql file?

[JimDiaz]

Yes this is a handy utility. I'm attempting to create procedures and views which will provide the same output as DBUnload - n but without items such as comment to preserve format, etc. My goal is to be able to run this as an event in remote databases to validate the schema after an event such as a passthrough. I intend to use the SysSource.Source whenever possible because I have noticed the SQLA interpretation changes depending on version.

In the end I will hash the results and compare this against a hash stored in a master schema table and if there is a mismatch send notification to the consolidated.

Rather large project but with COVID-19 I need something to do.

[former_SQLA_member1694938]

PowerDesigner can nicely reverse engineer the entire DB from an ODBC connection.