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

Solarwinds

Reference Link

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

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

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)

Solarwinds
Previous Post
SQL SERVER – UNION With TEXT DataType Returns Error but and UNION ALL Works – Reasons
Next Post
SQL SERVER – Filter In-Memory OLTP Tables in SSMS

Related Posts

6 Comments. Leave new

  • Once again a very Good initiative, this is gonna help to every one..thank you Pinal

    Reply
  • As all required fields are available in foreign_keys_columns view except foreign key name, we can use following query also.

    SELECT object_name(constraint_object_id) AS ForeignKey,
    OBJECT_NAME(parent_object_id) AS TableName,
    COL_NAME(parent_object_id,
    parent_column_id) AS ColumnName,
    OBJECT_NAME (referenced_object_id) AS ReferenceTableName,
    COL_NAME(referenced_object_id,
    referenced_column_id) AS ReferenceColumnName
    FROM sys.foreign_key_columns

    Reply
  • This script seems to make no provision for multi-column FKs. There should be an ORDER BY in both queries, but it should be on key order, not on column name.

    Reply

Leave a Reply

Menu