Create Table statement, what tables are referenced by foreign key constraints in a given table. It's a simple query on two information_schema tables—table_constraints, key_column_usage. But it's wordy, and for generality it needs parsmeters, so it's most useful as a stored procedure.
In fact two, one lists all foreign key references in a database's tables, the other lists all foreign key references for a specified table in a specified database.
CREATE PROCEDURE ListParentsForDb( pdb CHAR(64) )
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
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;
CREATE PROCEDURE ListParentsForTable( pdb CHAR(64), ptable CHAR(64) )
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
JOIN information_schema.key_column_usage AS u
USING( constraint_schema, constraint_name )
WHERE c.constraint_type = 'FOREIGN KEY'
AND u.table_schema = pdb
AND u.table_name = ptable
ORDER BY u.table_schema,u.table_name,u.column_name;
Last updated 13 Mar 2025
