How to find relationship between tables
Posted Under: How-To, Object Search, Query, SQL Server 2005, SQL Server 2008
As a DBA or even as a SQL Server Programmer, you may need a quick script to find relationship between tables. I found a useful script to do that in a SQL Server newsgroup. With some minor update, here is the script which lists the name of the parent table, child table, Referred table, foreing key table and name of the foreign key constraint. I hope you will find it useful. All credit goes to original author.
Select
object_name(rkeyid) Parent_Table,
object_name(fkeyid) Child_Table,
object_name(constid) FKey_Name,
c1.name FKey_Col,
c2.name Ref_KeyCol
From
sys.sysforeignkeys s
Inner join sys.syscolumns c1
on ( s.fkeyid = c1.id And s.fkey = c1.colid )
Inner join syscolumns c2
on ( s.rkeyid = c2.id And s.rkey = c2.colid )
Order by Parent_Table,Child_Table
Reader Comments
thanks buddy it was very useful for me becoz i never used sql 2005 and i have been trying very hard to open .mdf file now i got the solution thanks again
Hi Namwar,
Thank you.
This query helped me a lot.
I am still a learner so asking this question,
Is it possible that if i give a parent table name then it should give me all the child tables name related to that table