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


select table_count,
    cast(100 * loner_tables / table_count as decimal (14,2)) concat '%' 
    as loner_ratio
    count(distinct tab.tabschema concat '.' concat tab.tabname) 
    as table_count,
        case when  const.constname is null and  ref.constname is null then 1
        else 0 end) as loner_tables
    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 
        tab.type ='T'
        and tab.tabschema not like 'SYS%'
) loner_ratio  


  • 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


  • 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.

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.