Get list of all foreign key table and column names in SQL Server

by Heathesh 5. December 2014 02:04

I came across a problem whereby I wanted to retrieve the table and column names of all the foreign keys in SQL Server. The script I ended up using was fairly straight forward, but I thought it might be useful to someone else so I thought I should share it:

SELECT 
OBJECT_SCHEMA_NAME(fk.parent_object_id) ParentTableSchema, 
OBJECT_NAME(fk.parent_object_id) ParentTableName,
COL_NAME(fk.parent_object_id, fkc.parent_column_id) ParentTableColumn, 
OBJECT_SCHEMA_NAME(fk.referenced_object_id) ReferencedTableSchema, 
OBJECT_NAME(fk.referenced_object_id) ReferencedTableName,
COL_NAME(fk.referenced_object_id, fkc.referenced_column_id) ReferencedTableColumn
FROM SYS.foreign_keys FK
JOIN SYS.foreign_key_columns FKC ON FK.object_id = FKC.constraint_object_id

Happy scripting!

Tags: , ,

Sql Server Reporting Services (SSRS)

Comments are closed



Powered by BlogEngine.NET 1.5.0.7 (with enhancements by Heathesh)
Theme by Mads Kristensen (with tweeks by Heathesh)

Certifications

Microsoft Certified Professional

Microsoft Certified Technology Specialist

Answer Questions

 

Tag cloud

Calendar

<<  September 2017  >>
MoTuWeThFrSaSu
28293031123
45678910
11121314151617
18192021222324
2526272829301
2345678

View posts in large calendar

http://heathesh.com