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

Query below returns tables that refers with foregin keys to 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 pk_tco.table_name = 'table_name' -- enter table name here
      --and pk_tco.table_schema = 'schema_name'
order by foreign_table;

Columns

  • foreign_table - foreign table name with schema name - table you look for
  • ref - relationship symbol implicating FK and direction
  • primary_table - rerefenced table name preceded with schema name - table you provided as a parameter

Rows

  • One row represents one referencing table
  • Scope of rows: all tables that refers to table with provided name
  • Ordered by referencing table schema and name

Sample results

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