The query below lists the tables that are most referenced by other tables with foreign keys. Those are the dictionary tables such as person, product or store. In data warehouses those are dimension tables.

See also:

• Tables with most relationships
• Tables with most foreign keys

Query

select 
    concat(unique_constraint_schema, '.', referenced_table_name) as 'table',
    count(*) as 'references',
    count(distinct concat(constraint_schema, '.', table_name))
            as referencing_tables
from information_schema.referential_constraints
-- where constraint_schema = 'database name' -- provide database name here
group by unique_constraint_schema,
    referenced_table_name
order by 2 desc,
     referencing_tables desc;

Columns

• table - name of the table
• references - number of foreign keys referencing to this table
• referencing_tables - number of different tables referencing to this table

Rows

• One row: represents one table in a database (schema)
• Scope of rows: tables being used in foreign keys as primary key table
• Ordered by: number of foreign keys shown in descending order and number of referencing tables in descending order

Sample results

List of most referenced tables in sakila database. Staff table is referenced 3 times from 3 different tables.