Friday, March 13, 2009

Fun with Foreign Keys

SELECT object_name(SR.rkeyid) AS Master
     , RC.name                AS Master_Column
     , object_name(SR.fkeyid) AS Child
     , FC.name                AS Child_Column
     , object_name(SR.constid)AS FK_Name

FROM       sysreferences SR
INNER JOIN sys.columns   RC ON RC.Object_ID = SR.rkeyid AND RC.Column_ID = SR.rkey1
INNER JOIN sys.columns   FC ON FC.Object_ID = SR.fkeyid AND FC.Column_ID = SR.fkey1
ORDER BY object_name(SR.rkeyid)
       , object_name(SR.fkeyid)