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

My friend Vinod Kumar and I wrote SQL Server Interview Questions and Answers, and readers still send me interview questions. I have been saving them, and this weekly series gives short, practical answers. A memorized script may help in an interview, but real experience matters more.

Question: Write a script to list foreign key relationships, their column pairs, and the constraint name in the current SQL Server database.

Answer: Join sys.foreign_keys to sys.foreign_key_columns. A composite foreign key has one row for each paired column, so include constraint_column_id to keep its order. Resolve the names from the correct object ID and column ID at each end:

SELECT fk.name AS ConstraintName,
       ps.name AS ReferencingSchema, pt.name AS ReferencingTable,
       pc.name AS ReferencingColumn,
       rs.name AS ReferencedSchema, rt.name AS ReferencedTable,
       rc.name AS ReferencedColumn,
       fkc.constraint_column_id AS PairPosition
FROM sys.foreign_keys AS fk
JOIN sys.foreign_key_columns AS fkc
  ON fkc.constraint_object_id = fk.object_id
JOIN sys.tables AS pt ON pt.object_id = fk.parent_object_id
JOIN sys.schemas AS ps ON ps.schema_id = pt.schema_id
JOIN sys.columns AS pc
  ON pc.object_id = fkc.parent_object_id
 AND pc.column_id = fkc.parent_column_id
JOIN sys.tables AS rt ON rt.object_id = fk.referenced_object_id
JOIN sys.schemas AS rs ON rs.schema_id = rt.schema_id
JOIN sys.columns AS rc
  ON rc.object_id = fkc.referenced_object_id
 AND rc.column_id = fkc.referenced_column_id
ORDER BY ps.name, pt.name, fk.name, fkc.constraint_column_id;

Here, referencing means the table holding the foreign key; referenced means the table whose candidate key it points to. If the key has two columns, expect two result rows for that constraint, in pair order. Run the query in the database you want to inspect. Catalog visibility depends on your permissions.

This diagram shows why the query returns two rows for one composite constraint. Each referencing column maps to its corresponding referenced column:

Shorter versions of this query use OBJECT_NAME and COL_NAME and skip the schema. They work in a small database, but they hide which schema a table belongs to and make composite keys easy to misread. Qualifying schemas and keeping the pair order is the detail I want a candidate to explain, not merely memorize.

A composite foreign key is not one row, it is one row per column pair, so keep the pair order.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.