Jon Todd

If you’ve ever found yourself trying to determine which tables have a foreign key to a given table in MySQL the following query should do the trick.

Variables:

• MY_SCHEMA – name of schema the referenced table is in
• MY_TABLE – name of the table being referenced by foreign keys

Query:

SELECT
TABLE_SCHEMA,
TABLE_NAME,
COLUMN_NAME,
REFERENCED_COLUMN_NAME
FROM
information_schema.KEY_COLUMN_USAGE
WHERE
REFERENCED_TABLE_SCHEMA = 'MY_SCHEMA'  AND
REFERENCED_TABLE_NAME = 'MY_TABLE';