List all tables referenced by a specific table (by FK) in MySQL database

The query below lists all tables referenced by the foreign key for a specific table.

Query

select distinct concat(table_schema, '.', table_name) as foreign_table,
       '>-' as rel,
       concat(referenced_table_schema, '.', referenced_table_name) 
       as primary_table
from information_schema.key_column_usage
where referenced_table_name is not null
      and table_name = 'table name' -- provide table name here
--      and table_schema = 'database name'
order by primary_table;

Columns

  • foreign_table - name of the foreign table - the table you provided as a parameter
  • rel - relationship symbol implicating FK and direction
  • primary_table - primary (referenced) tables names - the tables you are looking for

Rows

  • One row: represents one referenced table
  • Scope of rows: all tables referenced by the provided table name
  • Ordered by: referencing table name

Sample results

All tables referenced by FK for the store table in the sakila database (schema).

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