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:

Query

select
    refFK.table,
    foreign_keys + coalesce(references,0) as relationships ,
    foreign_keys,
    coalesce(references,0) as references,
    referenced_tables + coalesce(referencing_tables,0) as related_tables,
    referenced_tables,
    coalesce(referencing_tables,0) as referencing_tables
from 
(   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

Columns

  • 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

Rows

  • 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

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