Sunday, October 16, 2011

SQL Command to know Parent-Child tables relationship

SELECT ParentTable.Name AS PTable, PK.Name AS PrimaryKeyForParent, FK.name AS ForeignKeyOfChild, ChildTable.name AS CTable

FROM SYS.foreign_keys FK

INNER JOIN SYS.OBJECTS ChildTable ON FK.parent_object_id = ChildTable.object_id

INNER JOIN SYS.OBJECTS ParentTable ON FK.referenced_object_id = ParentTable.object_id

LEFT OUTER JOIN SYS.key_constraints PK ON ParentTable.object_id = PK.parent_object_id

ORDER BY PTable