List tables with most relationships in Db2 database

Query below lists tables with most relationships (both foreign keys and FK references from other tables).

See also:


    foreign_keys + coalesce(references,0) as relationships ,
    coalesce(references,0) as references,
    referenced_tables + coalesce(referencing_tables,0) as related_tables,
    coalesce(referencing_tables,0) as referencing_tables
(   select 
    tabschema concat '.' concat tabname as table,
    count(constname) as foreign_keys,
    count(distinct reftabschema concat '.' concat reftabname)
                        as referenced_tables 
    from syscat.references
    group by tabschema concat '.' concat tabname
) refFK
left outer join
(   select 
    distinct reftabschema concat '.' concat reftabname as table,
    count(*) as references,
    count(distinct tabschema concat '.' concat tabname)
                    as referencing_tables 
    from syscat.references
    group by reftabschema concat '.' concat reftabname
) refbyFK on refFk.table = refbyFK.table
order by foreign_keys + COALESCE(references,0) desc


  • table - name of table with schema name
  • relationships - number of relationships (FKs and FK references)
  • foreign_keys - number of foreign keys in a table
  • references - number of FK references from other tables
  • related_tables - number of different related tables (regardless of relationship type/direction table is counted only once)
  • referenced_tables - nubmer of different tables referenced with FKs (please note that table can be related more than once so number of FKs and number of referenced tables can be different)
  • referencing_tables - nubmer of different tables referencing with foreign keys this table


  • One row represents one table in a database
  • Scope of rows: all tables in a database
  • Ordered by number of relationships (foreing keys and references) from the ones with the most

Sample results

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.