List table columns with their foreign keys in Db2 database

Query below returns all columns from all tables in a database with a foreign key refererence if column has one.

Query

select
    col.tabschema concat '.' concat col.tabname as table_name, 
    col.colno as column_id,
    col.colname as column_name,
    case when fk_constraint_name is not null then '>-' else null end as rel,
    const.primary_table,
    const.pk_column_name,
    const.no,
    const.fk_constraint_name    
from syscat.columns col
inner join syscat.tables tab on 
      tab.tabschema = col.tabschema and tab.tabname = col.tabname
left outer join 
(   select
        ref.tabschema,
        ref.tabname, 
        ref.constname as fk_constraint_name,
        key.colname  as fk_column_name,     
        ref.reftabschema concat '.' concat ref.reftabname as primary_table, 
        keypk.colname as pk_column_name, 
        ref.pk_colnames,    
        key.colseq  as no
    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 
) const on const.tabschema = col.tabschema and const.tabname = col.tabname 
            and const.fk_column_name = col.colname
where tab.type = 'T'
and tab.tabschema not like 'SYS%'  
order by table_name, column_id

Columns

  • table - table in a database with schema name
  • column_id - number of column in a database
  • column_name - column name
  • rel - relationship symbol ('>-') indicating foreign key and direction
  • primary_table - referenced table
  • pk_column_name - referenced column
  • no - column id in a key constraint
  • fk_constraint_name - foreign key constraint name

Rows

  • One row represents one column of every table in a database
  • Scope of rows: all columns from all tables in a database
  • Ordered by table schema and name, column id in a table

Sample results

A few columns in Sample database with their foregin keys:

Comments are only visible when the visitor has consented to statistics cookies. To see and add comments please accept statistics cookies.
0
There are no comments. Click here to write the first comment.