This article is half-done without your Comment! *** Please share your thoughts via Comment ***
In this post, I am sharing different scripts to find table dependency for Foreign Key
Constraint, View and Stored Procedure of MySQL.
Database Architecture or Database Developer has to find table dependency before any DDL changes on the Tables.
Below are three different scripts:
|
1 2 3 4 5 6 7 8 9 10 |
/*Find Table Dependency in Foreign Key Constraint.*/ SELECT Constraint_Type ,Constraint_Name ,Table_Schema ,Table_Name FROM information_schema.table_constraints WHERE Constraint_Type = 'FOREIGN KEY' AND Table_Name = 'Any_Table' AND Table_Schema ='db_Name'; |
|
1 2 3 4 5 |
/*Find Table Dependency in Views.*/ SELECT * FROM information_schema.views WHERE Table_Schema='db_name' AND Table_Name = 'table_name' |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
/*Find Table Dependency in Stored Procedure.*/ SELECT Table_Schema ,Table_Name ,Table_Type ,Engine ,Routine_Name ,Routine_Schema ,Routine_Type FROM information_schema.tables INNER JOIN information_schema.routines ON routines.routine_definition LIKE Concat('%', tables.table_name, '%') WHERE Table_Schema ='db_name' |
