Thursday, October 20, 2011

Find Tables With Foreign Key

SELECT FK.TABLE_NAME, CU.COLUMN_NAME,
PK.TABLE_NAME, PKT.COLUMN_NAME,
C.CONSTRAINT_NAME
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS C
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK
ON C.CONSTRAINT_NAME = FK.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK
ON C.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE CU
ON C.CONSTRAINT_NAME = CU.CONSTRAINT_NAME
INNER JOIN (
SELECT t1.TABLE_NAME, t2.COLUMN_NAME
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS t1
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE t2
ON t1.CONSTRAINT_NAME = t2.CONSTRAINT_NAME
WHERE t1.CONSTRAINT_TYPE = 'PRIMARY KEY') PKT
ON PKT.TABLE_NAME = PK.TABLE_NAME
-- TO LIMIT LIMIT QUERY RESULT UNCOMMENT
-- PRIMARY KEY
--WHERE PK.TABLE_NAME IN ('TABLE1', 'TABLE2')
-- FORGIGN KEY
--WHERE FK.TABLE_NAME IN ('one_thing', 'another')

No comments: