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:
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
- 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
Loner Ratio in GSDB database is 9% - i.e. 9% of tables are not related with FKs to any other table.