Find parent tables

from the Artful Common Queries page


We often need to know, without having to scan the 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;


Return to the Artful Common Queries page