SQL Server – List all Foreign Key Constraints of Database or Table

List all Foreign Key Constraints of Database or Table

How often we face this problem? The Foreign Key constraint of the table preventing us from deleting or modifying records.

To deal with them, first we need to have information about these constraints. How nice it would it be to get details of all the Foreign Key constraint defined in the Database or on a Table.

Good News, yes we can. Just use following script:

USE AdventureWorks

— List all Foreign Key Constraints in whole Database

SELECT o1.name AS Referencing_Object_name,

c1.name AS referencing_column_Name,

o2.name AS Referenced_Object_name,

c2.name AS Referenced_Column_Name,

s.name AS Constraint_name

FROM sysforeignkeys fk

INNER JOIN sysobjects o1 ON fk.fkeyid = o1.id

INNER JOIN sysobjects o2 ON fk.rkeyid = o2.id

INNER JOIN syscolumns c1 ON c1.id = o1.id AND c1.colid = fk.fkey

INNER JOIN syscolumns c2 ON c2.id = o2.id AND c2.colid = fk.rkey

INNER JOIN sysobjects s ON fk.constid = s.id


— List all Foreign Key Constraints in Table Customer

SELECT o1.name AS Referencing_Object_name,

c1.name AS referencing_column_Name,

o2.name AS Referenced_Object_name,

c2.name AS Referenced_Column_Name,

s.name AS Constraint_name

FROM sysforeignkeys fk

INNER JOIN sysobjects o1 ON fk.fkeyid = o1.id

INNER JOIN sysobjects o2 ON fk.rkeyid = o2.id

INNER JOIN syscolumns c1 ON c1.id = o1.id AND c1.colid = fk.fkey

INNER JOIN syscolumns c2 ON c2.id = o2.id AND c2.colid = fk.rkey

INNER JOIN sysobjects s ON fk.constid = s.id

AND o2.name=‘Customer’

See also:

SQL Server – List all Constraints of Database or Table

SQL Server – Drop All Table Constraints

Good Luck!

Advertisements

3 responses to this post.

  1. Very good article. Thanks for sharing. Kindly keep it up.

    Reply

  2. Posted by PhC on October 25, 2012 at 8:20 am

    It seems you completely omit the case where a fk references more than one col

    Reply

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: