Pages

Saturday, March 28, 2009

List all the Foreign Keys in a SQL Server

The below script will give you list of Foreign Keys in a database and columns which are part of the Foreign key definition.

If you don't know how to add a Foreign key on a table then you need to check out the "Adding Foreign Key on a Table" first.

SELECT

TC.CONSTRAINT_NAME,
TC.TABLE_NAME ,
KCU.COLUMN_NAME,
ORDINAL_POSITION AS COLUMN_POSITION

FROM
INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC, INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU
WHERE 1=1
AND TC.CONSTRAINT_NAME = KCU.CONSTRAINT_NAME
AND TC.TABLE_NAME = KCU.TABLE_NAME
AND TC.CONSTRAINT_TYPE = 'FOREIGN KEY'

There could be several ways to get the same kind of information but from my point of view this is most simple way to do it.

Please let me know if you have a query which is much simple than this.....

No comments: