Monday, April 1, 2013

SQL script to find Foreign key dependencies

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] 
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