How to find which mysql tables refer to a particular field in another table?
database
mysql
database-design
Software and digital electronics / Coding
2023-12-29 23:00
I have a mysql database where there are so many tables refer to each other.
Among them, I would like to find which table is referring to column columnX of tableY`.
This is extremely difficult to check all tables one by one.
Is there any easy way to find list of all of these references?
add comment
Answered by robin
2024-06-29 23:08
What you are looking for is achievable by a single SQL query as follows:
SELECT
TABLE_NAME,
COLUMN_NAME,
CONSTRAINT_NAME,
REFERENCED_TABLE_NAME,
REFERENCED_COLUMN_NAME
FROM
information_schema.KEY_COLUMN_USAGE
WHERE
REFERENCED_TABLE_NAME = 'your_table_name'
AND REFERENCED_COLUMN_NAME = 'your_field_name';
Replace your_table_name and your_field_name with what you are looking for.
Warning: table name might have prefix!
add comment