Today I was looking for the code to find all Foreign Keys in my table and it’s reference Table. I used the below code t oget that:
SELECT CAST(F.Name AS VarChar(255)) AS [Foreign Key Name],
CAST(C.Name AS VarChar(255)) AS [Foreign Table],
CAST(FC.Name AS VarChar(255)) AS [Foreign Column],
CAST(p.Name AS VarChar(255)) AS [Primary Table],
CAST(RC.Name AS VarChar(255)) AS [Primary Column]
FROM
sysobjects AS F INNER JOIN
sysobjects AS C ON F.Parent_obj = C.ID
INNER JOIN sysreferences AS R ON F.ID = R.ConstID
INNER JOIN sysobjects AS P ON R.RKeyID = p.ID
INNER JOIN syscolumns AS RC ON R.RKeyID = RC.ID AND R.RKey1 = RC.ColID
INNER JOIN syscolumns AS FC ON R.FKeyID = FC.ID AND R.FKey1 = FC.ColID
LEFT JOIN syscolumns AS RC2 ON R.RKeyID = RC2.ID AND R.RKey2 = RC.ColID LEFT JOIN syscolumns FC2 ON R.FKeyID = FC2.ID AND R.FKey2 = FC.ColID
WHERE F.Type = 'F'
ORDER BY CAST(P.Name AS VarChar(255))
CAST(C.Name AS VarChar(255)) AS [Foreign Table],
CAST(FC.Name AS VarChar(255)) AS [Foreign Column],
CAST(p.Name AS VarChar(255)) AS [Primary Table],
CAST(RC.Name AS VarChar(255)) AS [Primary Column]
FROM
sysobjects AS F INNER JOIN
sysobjects AS C ON F.Parent_obj = C.ID
INNER JOIN sysreferences AS R ON F.ID = R.ConstID
INNER JOIN sysobjects AS P ON R.RKeyID = p.ID
INNER JOIN syscolumns AS RC ON R.RKeyID = RC.ID AND R.RKey1 = RC.ColID
INNER JOIN syscolumns AS FC ON R.FKeyID = FC.ID AND R.FKey1 = FC.ColID
LEFT JOIN syscolumns AS RC2 ON R.RKeyID = RC2.ID AND R.RKey2 = RC.ColID LEFT JOIN syscolumns FC2 ON R.FKeyID = FC2.ID AND R.FKey2 = FC.ColID
WHERE F.Type = 'F'
ORDER BY CAST(P.Name AS VarChar(255))
No comments:
Post a Comment