List foreign keys with columns in Db2 database

Query below returns foreign key constrant columns defined in a database.

Query

select
        ref.tabschema concat '.' concat ref.tabname as foreign_table,
        '>-' as rel,
        ref.reftabschema concat '.' concat ref.reftabname as primary_table, 
        key.colseq  as no,
        key.colname  as fk_column_name,     
        ' = ' as join,
        keypk.colname as pk_column_name,     
        ref.constname as fk_constraint_name
 from syscat.references ref
 left outer join syscat.keycoluse key on 
        key.tabschema = ref.tabschema and key.tabname = ref.tabname 
        and key.constname = ref.constname
left outer join syscat.keycoluse keypk on 
        keypk.tabschema = ref.reftabschema 
        and keypk.tabname = ref.reftabname 
        and keypk.constname = ref.refkeyname 
        and keypk.colseq=key.colseq

Columns

  • foreign_table - foreign table name with schema name
  • rel - relationship symbol implicating direction
  • primary_table - primary (referenced) table name with schema name
  • no - id of the column in key. Single coumn keys always have 1, composite keys have 1, 2, ... n for each column of the key
  • fk_column_name - foreign table column
  • join - "=" symbol indicating join operation for pair of columns
  • pk_column_name - primary (referenced) table column
  • fk_constraint_name - foreign key constraint name

Rows

  • One row represents one foreign key column. If foreign key consists of multiple columns (composite key), each column appears separately.
  • Scope of rows: all foregin keys in a database and their columns
  • Ordered by foreign table schema name and table name and column ordinal posion in key

Sample results

Foreign keys in Sample database with their columns:

0
There are no comments. Click here to write the first comment.