Find cross-database foreign keys

from the Artful Common Queries page


SELECT 
  u.table_schema as 'RefSchema',
  u.table_name as 'RefTable',
  u.column_name as 'RefColumn',
  u.referenced_table_schema as 'Schema',
  u.referenced_table_name as 'Table',
  u.referenced_column_name as 'Column'
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 <> u.referenced_table_schema;
To find them for a particular database, add the WHERE condition:
AND 'dbname' IN(u.table_schema, u.referenced_table_schema)

Last updated 27 Nov 2011




Return to the Artful Common Queries page