Query below lists tables that are most referenced by other tables with foreign keys.
select pk_tco.table_schema || '.' || pk_tco.table_name as table_name, count(*) as references, count(distinct fk_tco.table_name) as referencing_tables 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 group by 1 order by count(*) desc;
- table_name - name of the table with schema name
- references - number of foreign keys referencing to this table
- referencing_tables - number of different tables referencing to this table
- One row represents one table in a database
- Scope of rows: tables being used in foreign keys as primary key table
- Ordered by number of references descending
List of most referenced tables in pagila database. public.store table is referenced 4 times from 3 different tables.