Find tables without relationships - Loner Tables - in Redshift

Bart Gawrych - Dataedo Team Bart Gawrych 2019-06-03

Table of Contents:


    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.

    By continuing to use our website, you consent to the use of cookies in accordance with our Privacy Policy (updated 28-04-2020).
    Accept