Microsoft SQL Server: How to Find the Foreign Keys in a Database

Contributed by Rob Rogers  
Tagged: Database  

A foreign key is a column or columns that are used to enforce a link between data in two tables. While SQL Server gives you no quick and easy way to view all foreign keys in a database, this quick query will give you that information. It comes in handy when trying to troubleshoot Foreign Key Constraint errors.

1. Open a New Query on the desired database.

2. 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

You can now easily see all foreign keys in that database.