The query below lists tables with their number of foreign keys and the number of tables to which they refer.

See also:

• Tables with most relationships
• Most referenced tables.

Query

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;

Columns

• 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.

Rows

• 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

Sample results

Tables in the sakila database (schema) showing first those that have the most keys: