Find parent tables

from the Artful Common Queries page


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) )
  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 c.table_schema = pdb
    AND u.referenced_table_name = ptable
  ORDER BY u.table_schema,u.table_name,u.column_name;


Return to the Artful Common Queries page