Wednesday, July 20, 2011

How to find relationship between tables

As a DBA or even as a SQL Server Programmer, we may need a quick script to find relationship  between tables. Here is the script which helped me a lot, hope this will be useful for you too

  1: SELECT  OBJECT_NAME(rkeyid) ParentTable
  2:        ,OBJECT_NAME(fkeyid) ChildTable
  3:        ,OBJECT_NAME(constid) FKey
  4:        ,c1.name FKey_Col
  5:        ,c2.name Ref_KeyCol
  6: FROM    sys.sysforeignkeys s
  7: INNER JOIN sys.syscolumns c1 ON ( s.fkeyid = c1.id
  8:                                   AND s.fkey = c1.colid
  9:                                 )
 10: INNER JOIN syscolumns c2 ON ( s.rkeyid = c2.id
 11:                               AND s.rkey = c2.colid
 12:                             )
 13: ORDER BY 1,2

0 comments:

Post a Comment