Find tables without relationships - Loner Tables - in Redshift

Query below aggregates two other queries we have published in Query Toolbox: Tables without FK and Not referenced tables . Not related tables we call Loner Tables. Look at the diagram to get the concept:

Learn more about Loner Tables

Query

select distinct 
       'No FKs >-' as refs,
       tab.table_schema,
       tab.table_name,
       '>- no FKs' as foreign_keys
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'
      and rel.table_name is null
order by tab.table_schema,
         tab.table_name;

Columns

  • refs - symbol implicating lack of references by foregin key
  • table_schema - table schema
  • table_name - table name
  • foreign_keys - symbol implicating lack of foregin key

Rows

  • One row represents one table
  • Scope of rows: tables without relationships
  • Ordered by table schema and table name

Sample results

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