Identify Cross Database Foreign Keys

I’ve blogged before about cross-database foreign keys and what I think of them. I had a developer wanting to check for such references between two databases today. Here’s what I came up with to do this…

SELECT t.NAME AS TABLE_NAME,
		i.NAME AS INDEX_NAME,
		f.*
FROM information_schema.`INNODB_SYS_FOREIGN` f
INNER JOIN information_schema.`INNODB_SYS_INDEXES` i
	ON i.`NAME` = SUBSTRING(f.`ID`, LOCATE('/', f.`ID`) + 1)
INNER JOIN information_schema.`INNODB_SYS_TABLES` t
	ON t.TABLE_ID = i.TABLE_ID
WHERE (f.FOR_NAME LIKE 'db1%' AND f.REF_NAME LIKE 'db2%')
OR (f.REF_NAME LIKE 'db1%' AND f.FOR_NAME LIKE 'db2%');

N.B. This query only applies to the innodb storage engine.