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)




Return to the Artful Common Queries page