List all tables refrenced by specific table (by FK) in Redshift

Query below lists all tables refrenced with foregin key by specific table.

Query

select distinct 
       fk_tco.table_schema || '.' || fk_tco.table_name as foreign_table,
       '>-' as rel,
       pk_tco.table_schema || '.' || pk_tco.table_name as primary_table
from information_schema.referential_constraints rco
join information_schema.table_constraints fk_tco 
          on rco.constraint_name = fk_tco.constraint_name
          and rco.constraint_schema = fk_tco.table_schema
join information_schema.table_constraints pk_tco
          on rco.unique_constraint_name = pk_tco.constraint_name
          and rco.unique_constraint_schema = pk_tco.table_schema
where fk_tco.table_name = 'sales2' -- enter table name here
      --and fk_tco.table_schema = 'schema_name'
order by foreign_table;

Columns

  • foreign_table - foreign table name with schema name - the table you provided as a parameter
  • rel - relationship symbol implicating FK and direction
  • primary_table - primary (rerefenced) tables names with schema name - the tables you are looking for

Rows

  • One row represents one referenced table
  • Scope of rows: all tables referenced by table with provided name (and optionally schema)
  • Ordered by referenced table schema and name

Sample results

All tables referenced with FK by public.sales2 table in out test database.

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