Query below return number of tables which are not referencing and are not referenced by other tables (Loner Tables).

Learn more about Loner Tables

Query

with loners as (
    select distinct tab.table_schema || '.' || tab.table_name as table,
           case when (fks.constraint_id is null and ref.constraint_id is null)
            then 1
            else 0 end as is_loner
    from v_catalog.tables tab
    left join v_catalog.foreign_keys fks
              on tab.table_schema = fks.table_schema
              and tab.table_name = fks.table_name
    left join v_catalog.foreign_keys ref
              on tab.table_schema = ref.reference_table_schema
              and tab.table_name = ref.reference_table_name
)
select count(*) as tables,
       sum(is_loner) as loner_tables,
       100*round(sum(is_loner)/count(*),2) as loner_ratio
from loners;

Columns

• tables - number of all tables in database
• loner_tables - number of not related tables in the database
• loner_ratio - Loner Ratio - % of Loner Tables in the database

Rows

• Scope of rows: query returns one row

Sample results