Query below lists all tables refrenced with foregin key by specific table.
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;
- 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
- 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
- There can be more tables with the same name. If that's the case, uncomment where clause and provide schema name
All tables referenced with FK by public.rental table in pagila database.