Last active
August 29, 2015 14:08
-
-
Save nicka101/04ffecad8d41079fe8cd to your computer and use it in GitHub Desktop.
Return a list of tables which depend on the specified table
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
CREATE PROCEDURE table_dependents | |
( | |
IN tbl_name varchar(255) | |
) | |
BEGIN | |
CREATE TEMPORARY TABLE tbl_dependents_processing( | |
table_name varchar(255) PRIMARY KEY NOT NULL | |
)ENGINE=MEMORY; | |
INSERT INTO tbl_dependents_processing SELECT DISTINCT TABLE_NAME FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE REFERENCED_TABLE_NAME = tbl_name; | |
CREATE TEMPORARY TABLE tbl_dependents_done( | |
table_name varchar(255) PRIMARY KEY NOT NULL | |
)ENGINE=MEMORY; | |
WHILE (SELECT COUNT(*) FROM tbl_dependents_processing) <> 0 DO | |
SET @processing_table = ''; | |
SELECT table_name INTO @processing_table FROM tbl_dependents_processing LIMIT 1; | |
DELETE FROM tbl_dependents_processing WHERE table_name = @processing_table; | |
INSERT IGNORE INTO tbl_dependents_done VALUE(@processing_table); | |
INSERT IGNORE INTO tbl_dependents_processing SELECT DISTINCT TABLE_NAME FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE REFERENCED_TABLE_NAME = @processing_table; | |
DELETE FROM tbl_dependents_processing WHERE table_name IN (SELECT * FROM tbl_dependents_done); | |
END WHILE; | |
SELECT * FROM tbl_dependents_done; | |
END; |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
CREATE PROCEDURE table_dependents | |
( | |
IN tbl_name varchar(255) | |
) | |
BEGIN | |
DECLARE v_done tinyint unsigned default 0; | |
CREATE TEMPORARY TABLE tbl_dependents_1( | |
table_name varchar(255) PRIMARY KEY NOT NULL | |
)ENGINE=MEMORY; | |
INSERT INTO tbl_dependents_1 SELECT DISTINCT TABLE_NAME FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE REFERENCED_TABLE_NAME = tbl_name; | |
CREATE TEMPORARY TABLE tbl_dependents_2 ENGINE=MEMORY SELECT * FROM tbl_dependents_1; | |
CREATE TEMPORARY TABLE tbl_dependents_3( | |
table_name varchar(255) PRIMARY KEY NOT NULL | |
)ENGINE=MEMORY; | |
/* http://dev.mysql.com/doc/refman/5.0/en/temporary-table-problems.html */ | |
WHILE v_done = 0 DO | |
INSERT INTO tbl_dependents_3 SELECT DISTINCT TABLE_NAME FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE REFERENCED_TABLE_NAME IN (SELECT * FROM tbl_dependents_1) UNION SELECT * FROM tbl_dependents_2; | |
IF (SELECT COUNT(*) FROM tbl_dependents_1) = (SELECT COUNT(*) FROM tbl_dependents_3) THEN | |
SET v_done = 1; | |
ELSE | |
INSERT IGNORE INTO tbl_dependents_1 SELECT * FROM tbl_dependents_3; | |
INSERT IGNORE INTO tbl_dependents_2 SELECT * FROM tbl_dependents_3; | |
TRUNCATE TABLE tbl_dependents_3; | |
END IF; | |
END WHILE; | |
SELECT * FROM tbl_dependents_3; | |
END; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment