8 Oct 2011

Find refrence of table in SQL Server 2008

SELECT     cast(f.name AS VARCHAR(255)) AS foreign_key_name
           ,r.keycnt
           ,cast(c.name AS VARCHAR(255)) AS foreign_table
           ,cast(fc.name AS VARCHAR(255)) AS foreign_column_1
           ,cast(fc2.name AS VARCHAR(255)) AS foreign_column_2
           ,cast(p.name AS VARCHAR(255)) AS primary_table
           ,cast(rc.name AS VARCHAR(255)) AS primary_column_1
           ,cast(rc2.name AS VARCHAR(255)) AS primary_column_2
FROM       sysobjects f
INNER JOIN sysobjects c ON f.parent_obj = c.id
INNER JOIN sysreferences r ON f.id = r.constid
INNER JOIN sysobjects p ON r.rkeyid = p.id
INNER JOIN syscolumns rc ON r.rkeyid = rc.id
                            AND r.rkey1 = rc.colid
INNER JOIN syscolumns fc ON r.fkeyid = fc.id
                            AND r.fkey1 = fc.colid
LEFT JOIN  syscolumns 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'
           AND p.name = 'Mtrghighendprg'
ORDER      BY cast(p.name AS VARCHAR(255))

No comments:

Post a Comment