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)



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

<<  June 2017  >>
MoTuWeThFrSaSu
2930311234
567891011
12131415161718
19202122232425
262728293012
3456789

View posts in large calendar

http://heathesh.com