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

Microsoft SQL Server: How to Find the Foreign Keys in a 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.

Rob Rogers
Rob Rogers
Once a prolific author here on Tech-Recipes, Rob has moved on to greener pastures.
RELATED ARTICLES

1 COMMENT

LEAVE A REPLY

Please enter your comment!
Please enter your name here

- Advertisment -

Most Popular

Recent Comments

Cleveland Furia on How to Clear Google Activity
Sid Wohlfarth on How to Clear Google Activity
Riley Magsayo on How to Clear Google Activity
สมัครรับเครดิตฟรี ทันที on How to improve Remote Desktop Protocol Performance
Neal Wangstad on How to Clear Google Activity
Slavcho Andov on How to install Ubuntu
Slavcho Andov on How to fix bad sectors
Sheraz Ali on How to Backup Android