List all tables refrenced by specific table (by FK) in PostgreSQL database

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

Query

select distinct 
       fk_tco.table_schema || '.' || fk_tco.table_name as fk_table_name,
       '>-' as rel,
       pk_tco.table_schema || '.' || pk_tco.table_name as primary_table_name
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 = 'table_name' -- enter table name here
      --and fk_tco.table_schema = 'schema_name'
order by fk_table_name;

Columns

  • fk_table_name - foreign table name with schema name - the table you provided as a parameter
  • rel - relationship symbol implicating FK and direction
  • primary_table_name - 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

Notes

  • There can be more tables with the same name. If that's the case, uncomment where clause and provide schema name

Sample results

All tables referenced with FK by public.rental table in pagila database.

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.