Loner Ratio - how many tables have no relationships in Redshift

Query below returns number of tables that don't have foreign keys and are not referenced by other tables with foreign keys.

This diagram illustrates Loner Tables concept:

Query

select count(distinct tab.table_schema || '.' || tab.table_name) as tables,
       count(distinct tab.table_schema || '.' || tab.table_name)
            - count(distinct rel.table_schema || '.' || rel.table_name)
            as loner_tables,
       round(100.0*(count(distinct tab.table_schema || '.' || tab.table_name)
            - count(distinct rel.table_schema || '.' || rel.table_name))
            /count(distinct tab.table_schema || '.' || tab.table_name),2) || '%'
            as loner_ratio
from information_schema.tables tab
left join (
          select distinct tco.table_schema ,tco.table_name 
          from information_schema.referential_constraints rco
          join information_schema.table_constraints tco 
               on rco.unique_constraint_name = tco.constraint_name
               and rco.unique_constraint_schema = tco.table_schema
          union all
          select distinct tco.table_schema, tco.table_name 
          from information_schema.referential_constraints rco
          join information_schema.table_constraints tco 
               on rco.constraint_name = tco.constraint_name
               and rco.constraint_schema = tco.table_schema
) rel on rel.table_name = tab.table_name 
    and rel.table_schema = tab.table_schema
where tab.table_schema not in ('information_schema', 'pg_catalog') 
      and tab.table_type ='BASE TABLE';

Columns

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

Rows

  • Scope of rows: query returns one row

Sample results

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