Loner Ratio - how many tables have no relationships in Db2 database

This query listed tables that have no foreign keys, meaning they are not referencing any table or are not on the "many" side of FK.

Query below lists something a little different - tables that are not referencing and are not referenced by other tables. Something we called Loner Tables. This diagram illustrates the concept:

Learn more about Loner Tables

Query

select table_count,
    loner_tables,
    cast(100 * loner_tables / table_count as decimal (14,2)) concat '%' 
    as loner_ratio
from
(select 
    count(distinct tab.tabschema concat '.' concat tab.tabname) 
    as table_count,
    sum(
        case when  const.constname is null and  ref.constname is null then 1
        else 0 end) as loner_tables
    from 
    syscat.tables tab
    left outer join syscat.tabconst const 
        on const.tabschema = tab.tabschema 
        and const.tabname = tab.tabname and const.type ='F'
    left outer join syscat.references ref 
        on ref.reftabschema = tab.tabschema  
        and ref.reftabname = tab.tabname 
    where 
        tab.type ='T'
        and tab.tabschema not like 'SYS%'
) loner_ratio  

Columns

  • table_count - number of tables in database
  • loner_tables - number of Loner Tables in the database
  • loner_ratio - Loner Ratio - % of Loner Tables in the database

Rows

  • Scope of rows: query returns one row

Sample results

Loner Ratio in GSDB database is 9% - i.e. 9% of tables are not related with FKs to any other table.

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