I want to drop a table but it is referenced by one or more other tables. How can I find out which tables are referencing this table without having to look at each of the tables in the database one by one?
8 Answers
SELECT TABLE_NAME
FROM information_schema.KEY_COLUMN_USAGE
WHERE TABLE_SCHEMA = 'your_schema_name'
AND REFERENCED_TABLE_NAME = 'your_table_name';
This works.
select table_name
from information_schema.referential_constraints
where referenced_table_name = 'parent table here';
Use Toad to load it up and you can view the references through the diagram. also make sure that you don't have any app code passing sql from the front-end, dropping the table may cause the app to break.
Download link http://www.toadsoft.com/toadmysql/FreewareDownload.htm
If you are using innoDB try this one SHOW TABLE STATUS FROM yourdatabasename LIKE 'T' http://dev.mysql.com/doc/refman/5.1/en/show-table-status.html
If you also want the specific column where the references are made use the following query.
SELECT
ku.CONSTRAINT_NAME AS "Foreign key",
CONCAT("`", ku.TABLE_SCHEMA, "`.`", ku.TABLE_NAME, "`") AS "In",
GROUP_CONCAT(ku.COLUMN_NAME) AS "Source column",
CONCAT("`", ku.REFERENCED_TABLE_SCHEMA, "`.`", ku.REFERENCED_TABLE_NAME, "`") AS
"References",
GROUP_CONCAT(ku.REFERENCED_COLUMN_NAME) AS "Target column"
FROM information_schema.KEY_COLUMN_USAGE AS ku
WHERE ku.REFERENCED_TABLE_SCHEMA = '[THE_CURRENT_SELECTED_SCHEMA_NAME]'
AND ku.REFERENCED_TABLE_NAME = '[THE_CURRENT_SELECTED_TABLE_NAME]'
GROUP BY ku.CONSTRAINT_NAME
HAVING `In` != `References`
Related