List tables with most relationships in MySQL database

The query below lists tables with most relationships (both foreign keys and FK references from other tables).

See also:

Query

 select concat(database_name, '.', table_name) as 'table',
        count(referenced_table) + count(referencing_table) as relationships,
        count(referenced_table) as foreign_keys,
        count(referencing_table) as 'references',
        count(distinct related_table) as related_tables,
        count(distinct referenced_table) as referenced_tables,
        count(distinct referencing_table) as referencing_tables
from (
     (select constraint_schema
                as database_name,
           table_name
                as table_name,
           concat(unique_constraint_schema, '.', referenced_table_name)
                 as referenced_table,
           null as referencing_table,
           concat(unique_constraint_schema, '.', referenced_table_name)
                 as related_table
     from information_schema.referential_constraints)
union all
     (select unique_constraint_schema as database_name,
             referenced_table_name as table_name,
             null as referenced_table,
             concat(constraint_schema, '.', table_name)
                    as referencing_table,
             concat(constraint_schema, '.', table_name)
                   as related_table
      from information_schema.referential_constraints)
) x
group by database_name,
         table_name
order by relationships desc;

Columns

  • table - name of the table
  • relationships - number of relationships (FKs and FK references)
  • foreign_keys - number of foreign keys in a table
  • references - number of FK references from other tables
  • related_tables - number of different related tables (regardless of relationship type/direction table is counted only once)
  • referenced_tables - number of different tables referenced with FKs (please note that table can be related more than once so number of FKs and number of referenced tables can be different)
  • referencing_tables - number of different tables referencing with foreign keys this table

Rows

  • One row: represents one table in a database (schema)
  • Scope of rows: all tables in a database (schema)
  • Ordered by: number of relationships (foreign keys and references) showing the ones with the most first

Sample results

Sample results in the Sakila database (schema)

Comments are only visible when the visitor has consented to statistics cookies. To see and add comments please accept statistics cookies.
0
There are no comments. Click here to write the first comment.