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.


    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,
from syscat.columns col
inner join syscat.tables tab on 
      tab.tabschema = col.tabschema and tab.tabname = col.tabname
left outer join 
(   select
        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, 
        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


  • 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


  • 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.
There are no comments. Click here to write the first comment.