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

The query below lists all tables that reference a specific table with foreign keys.

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 = 'table name' -- provide table name here
--      and table_schema = 'database name'
order by foreign_table;

Columns

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

Rows

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

Sample results

All referencing tables with FK for the address table in the sakila database (schema).

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