mysql · database · how-to · mariadb

How to view all related tables in Mysql/MariaDB?

Problem

You want to view all tables related to specific table by foreign keys. Or you'd like to see all tables related to specific column.

Solution

You can use this simple query:
For specific table

SELECT  
  TABLE_NAME, COLUMN_NAME, CONSTRAINT_NAME, REFERENCED_TABLE_NAME, REFERENCED_COLUMN_NAME
FROM  
  INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE  
  REFERENCED_TABLE_SCHEMA = 'schema_name' 
  AND REFERENCED_TABLE_NAME = 'table_name';

For specific column

SELECT  
  TABLE_NAME, COLUMN_NAME, CONSTRAINT_NAME, REFERENCED_TABLE_NAME, REFERENCED_COLUMN_NAME
FROM  
  INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE  
  REFERENCED_TABLE_SCHEMA = 'schema_name' 
  AND REFERENCED_TABLE_NAME = 'table_name' 
  AND REFERENCED_COLUMN_NAME = 'column_name';

Read also

MySQL INFORMATION_SCHEMA tables

Published:
comments powered by Disqus