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

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.