Friday, October 14, 2011

Find SQL Sever all the table's foreign key list using sql querys

Following query will list out all tables foreign key's


SELECT kcuf.TABLE_NAME AS 'Table Name', kcup.TABLE_NAME AS 'Reference table name',
kcup.COLUMN_NAME AS 'Reference table column name'
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS rc
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcup
ON kcup.CONSTRAINT_SCHEMA = rc.UNIQUE_CONSTRAINT_SCHEMA
AND kcup.CONSTRAINT_NAME = rc.UNIQUE_CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcuf
ON kcuf.CONSTRAINT_SCHEMA = rc.CONSTRAINT_SCHEMA
AND kcuf.CONSTRAINT_NAME = rc.CONSTRAINT_NAME
AND kcup.ORDINAL_POSITION = kcuf.ORDINAL_POSITION
ORDER BY kcuf.TABLE_NAME



Happy Coding..

No comments: