Find tables that are not referenced by the foreign keys in Redshift

Query below returns not referenced tables by the foreign keys.

See also:

Query

select '>- no FKs' as foreign_keys,
       tab.table_schema,
       tab.table_name
from information_schema.tables tab
where tab.table_schema not in ('information_schema', 'pg_catalog')
      and tab.table_type ='BASE TABLE'
      and tab.table_schema || '.' || tab.table_name not in
          (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)
order by tab.table_schema,
         tab.table_name;

Columns

  • foreign_keys - symbol indicating lack of FK references
  • table_schema - table schema name
  • table_name - table name

Rows

  • One row represents one table
  • Scope of rows: tables in a database which are not referenced by the foreign keys
  • Ordered by schema name, table name

Sample results

Comments are only visible when the visitor has consented to statistics cookies. To see and add comments please accept statistics cookies.
0
There are no comments. Click here to write the first comment.