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

<<  July 2017  >>
MoTuWeThFrSaSu
262728293012
3456789
10111213141516
17181920212223
24252627282930
31123456

View posts in large calendar

http://heathesh.com