Microsoft SQL Server: Got Foreign Key Constraint Errors? Here’s How to Find the Foreign Keys in a Database

July 1, 2012   SQL

A foreign key is used to enforce a link between data in two tables. If you are encountering Foreign Key Constraint Errors on a third party database, you can end up pulling out your hair trying to figure it out as SQL Server gives you no quick and easy way to view all foreign keys in a database. There is hope though, as you can list all Foreign Key Constraints in the desired database by executing a query aqainst the database.

Step 1 – Launch SQL Server Management Studio.

Step 2 – Open a New Query on the desired database.

Step 3 – Copy, paste, and execute the following:

SELECT FKConstraintName = a.CONSTRAINT_NAME,
FKTable = b.TABLE_NAME,
FKColumn = c.COLUMN_NAME,
PKTable = d.TABLE_NAME,
PKColumn = e.COLUMN_NAME
 
FROM
INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS a
INNER JOIN
INFORMATION_SCHEMA.TABLE_CONSTRAINTS b
ON a.CONSTRAINT_NAME = b.CONSTRAINT_NAME
INNER JOIN
INFORMATION_SCHEMA.TABLE_CONSTRAINTS d
ON a.UNIQUE_CONSTRAINT_NAME = d.CONSTRAINT_NAME
INNER JOIN
INFORMATION_SCHEMA.KEY_COLUMN_USAGE c
ON a.CONSTRAINT_NAME = c.CONSTRAINT_NAME
INNER JOIN
(
SELECT
f.TABLE_NAME, g.COLUMN_NAME
 
FROM
INFORMATION_SCHEMA.TABLE_CONSTRAINTS f
INNER JOIN
INFORMATION_SCHEMA.KEY_COLUMN_USAGE g
ON f.CONSTRAINT_NAME = g.CONSTRAINT_NAME
WHERE f.CONSTRAINT_TYPE = ‘PRIMARY KEY’
) e
 
ON e.TABLE_NAME = d.TABLE_NAME
 
ORDER BY a.CONSTRAINT_NAME
, , , , , , , , , ,