Wednesday, September 21, 2016

Documenting Foreign Keys in SQL 2012

SELECT F.name
     , PT.name AS FromTab
     , PC.name AS FromCol
     , CT.name AS ToTab
     , CC.name AS ToCol
FROM       sys.foreign_keys        F
INNER JOIN sys.foreign_key_columns FK ON FK.constraint_object_id = F.object_id
inner join sys.tables              PT ON PT.object_id            = FK.parent_object_id
inner join sys.tables              CT ON CT.object_id            = FK.referenced_object_id
                                                               
inner join sys.columns             PC ON PC.object_id            = FK.parent_object_id
                                     AND PC.column_id            = FK.parent_column_id
inner join sys.columns             CC ON CC.object_id            = FK.referenced_object_id
                                     AND CC.column_id            = FK.referenced_column_id

ORDER BY PT.name, PC.column_id


No comments:

Post a Comment