List all tables referencing a specific table (by FK) in MariaDB 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).

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.