List tables with most relationships in MariaDB database

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

See also:


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,
                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,
order by relationships desc;


  • 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


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

There are no comments. Click here to write the first comment.