Interview Question of the Week #001 – Script to List Foreign Key Relationships and Constraint Name

Two years ago I wrote a book SQL Server Interview Questions and Answers with my very close friend Vinod Kumar. It is a very popular book of mine and has sold many thousands of copies so far. Every single day I still get quite a many different interview questions in email and users wants me to answer them. I have been collecting them for quite a while and in this new year, I am starting this new series, which will take a one interview question sent by readers and will try to answer in short and simple words. The goal of this series is to build a knowledge on the subject. If you want to pass any interview, you should know much more than this question and answers. You should have real world experience. Read this blog post over here before you continue reading this story.

Well, let us start this series. In this week’s episode we will try to answer following question –

Question: Write a script to display foreign key relationships and name of the constraint for each table in the database for SQL Server 2005 and later version.

Answer: Here is the script which will display foreign key relationships and name of the constraint for each table in the database.

SQL Server 2005 and later version

SELECT f.name AS ForeignKey,
OBJECT_NAME(f.parent_object_id) AS TableName,
COL_NAME(fc.parent_object_id,
fc.parent_column_id) AS ColumnName,
OBJECT_NAME (f.referenced_object_id) AS ReferenceTableName,
COL_NAME(fc.referenced_object_id,
fc.referenced_column_id) AS ReferenceColumnName
FROM sys.foreign_keys AS f
INNER JOIN sys.foreign_key_columns AS fc
ON f.OBJECT_ID = fc.constraint_object_id

Reference Link

If you execute the above query with Adventure sample database, it will give following result set.

SQL Server 2000

SELECT
K_Table = FK.TABLE_NAME,
FK_Column = CU.COLUMN_NAME,
PK_Table = PK.TABLE_NAME,
PK_Column = PT.COLUMN_NAME,
Constraint_Name = C.CONSTRAINT_NAME
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS C
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK ON C.CONSTRAINT_NAME = FK.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK ON C.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE CU ON C.CONSTRAINT_NAME = CU.CONSTRAINT_NAME
INNER JOIN (
SELECT i1.TABLE_NAME, i2.COLUMN_NAME
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS i1
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE i2 ON i1.CONSTRAINT_NAME = i2.CONSTRAINT_NAME
WHERE i1.CONSTRAINT_TYPE = 'PRIMARY KEY'
) PT ON PT.TABLE_NAME = PK.TABLE_NAME
---- optional:
ORDER BY
1,2,3,4

Reference Link

If you have a better answer, please leave a comment and I will include the answer with due credit.

Reference: Pinal Dave (https://blog.sqlauthority.com)