Rajanand Ilangovan
Rajanand Ilangovan

Rajanand Ilangovan

List of all foreign keys and referenced tables and columns

SQL Server

Rajanand Ilangovan's photo
Rajanand Ilangovan
·Apr 11, 2022·

1 min read

Query

-- to get list of foreign keys and related tables and columns.
SELECT fk.name AS foreign_key_name,
pt.name AS parent_table_name,
pc.name AS parent_column_name,
ct.name AS referenced_table_name,
cc.name AS referenced_column_name
FROM sys.foreign_keys fk 
INNER JOIN sys.foreign_key_columns fkc ON fkc.constraint_object_id = fk.object_id
INNER JOIN sys.tables pt ON pt.object_id = fk.parent_object_id
INNER JOIN sys.columns pc ON fkc.parent_column_id = pc.column_id AND pc.object_id = pt.object_id 
INNER JOIN sys.tables ct ON ct.object_id = fk.referenced_object_id
INNER JOIN sys.columns cc ON cc.object_id = ct.object_id AND fkc.referenced_column_id = cc.column_id
ORDER BY pt.name, pc.name;

Sample Output

image.png

Impressum