Query below lists tables with their number of foreign keys and number of tables they refer to.
select fk_tco.table_schema || '.' || fk_tco.table_name as table_name, count(*) as foreign_keys, count(distinct pk_tco.table_name) as referenced_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 - table name with schema name
- foreign_keys - number of foreign keys in a table
- referenced_tables - number of referenced tables. Note that it is not the same as number of foreign keys, as multiple foreign keys may reference the same table.
- One row represents one table in a database
- Scope of rows: tables in a database that have foreign keys (reference other tables)
- Ordered by number of foreign keys from the ones with the most
Tables in pagila database with most foreign keys: