Monday, August 22, 2011

SQL SERVER : Query to get list of all the Foreign Key Relationships in the Database

1.  Use this one if you want to show the name of the primary key table and column name.
SELECT
FK.TABLE_NAME AS FKTable,
CU.COLUMN_NAME AS FKColumn,
C.CONSTRAINT_NAME AS FKConstraintName,
PK.TABLE_NAME AS PKTable,
DT.COLUMN_NAME AS PKColumn
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 TC.TABLE_NAME, TU.COLUMN_NAME
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE TU ON TC.CONSTRAINT_NAME = TU.CONSTRAINT_NAME
WHERE tc.CONSTRAINT_TYPE = 'PRIMARY KEY'
) DT ON DT.TABLE_NAME = PK.TABLE_NAME
ORDER BY CU.COLUMN_NAME, FK.TABLE_NAME




2.  Use this one if you want to get only table name and constraint name. 


SELECT TABLE_NAME, CONSTRAINT_NAME
    FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
    WHERE CONSTRAINT_TYPE = 'FOREIGN KEY'


No comments:

Post a Comment