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!