The query below lists tables with their number of foreign keys and the number of tables to which they refer.
select concat(constraint_schema, '.', table_name) as 'table', count(*) as foreign_keys, count(distinct concat(unique_constraint_schema, '.', referenced_table_name)) as referenced_tables from information_schema.referential_constraints -- where constraint_schema = 'database name' -- provide database name here group by constraint_schema, table_name order by foreign_keys desc, referenced_tables desc;
- table - name of the table
- 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 (schema)
- Scope of rows: tables in a database (schema) that have foreign keys (reference to other tables)
- Ordered by: number of foreign keys and number of referenced tables in descending order
Tables in the sakila database (schema) showing first those that have the most keys: