Find parent tables
List tables which are referenced by foreign key constraints in a given table.This is a simple query on two information_schema tables: table_constraints and key_column_usage. It is easy to parameterise, so we show it in stored procedures. The first sproc lists all foreign key references in a database. The second lists all foreign key references for a table.
CREATE PROCEDURE ListParentsForDb( pdb CHAR(64) )
BEGIN
SELECT
u.table_schema AS 'Schema',
u.table_name AS 'Table',
u.column_name AS 'Key',
u.referenced_table_schema AS 'Parent Schema',
u.referenced_table_name AS 'Parent table',
u.referenced_column_name AS 'Parent key'
FROM information_schema.table_constraints AS c
INNER JOIN information_schema.key_column_usage AS u
USING( constraint_schema, constraint_name )
WHERE c.constraint_type = 'FOREIGN KEY'
AND c.table_schema = pdb
ORDER BY u.table_schema,u.table_name,u.column_name;
END;
CREATE PROCEDURE ListParentsForTable( pdb CHAR(64), ptable CHAR(64) )
BEGIN
SELECT
u.table_schema AS 'Schema',
u.table_name AS 'Table',
u.column_name AS 'Key',
u.referenced_table_schema AS 'Parent Schema',
u.referenced_table_name AS 'Parent table',
u.referenced_column_name AS 'Parent key'
FROM information_schema.table_constraints AS c
INNER JOIN information_schema.key_column_usage AS u
USING( constraint_schema, constraint_name )
WHERE c.constraint_type = 'FOREIGN KEY'
AND c.table_schema = pdb
AND u.referenced_table_name = ptable
ORDER BY u.table_schema,u.table_name,u.column_name;
END;