Microsoft SQL Server: How to Find the Foreign Keys in a Database
Posted October 4, 2008 by Rob Rogers in 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.
About Rob Rogers
View more articles by Rob Rogers
The Conversation
Follow the reactions below and share your own thoughts.