How to find foreign key relationship between tables in the MS SQL database

 

Below is one of my favorite query I always use to find the foreign key relationship between tables in MS SQL database. I don't remmebr from which site I have collected this to share with you,


Anyways below query will give you complete details about the foriegn key relationship between the tables in the database. Once you have the list it won't be hard to find the table you are looking for.


Select object_name(rkeyid) [Master_Table_Name], object_name(fkeyid) [Child_Table_Name], SysCol.Name ForiegnKey_Column_Name, SysCol2.name Reference_Key_Column_Name
From sys.sysforeignkeys SysFrKey
Inner join sys.syscolumns SysCol on (SysFrKey.fkeyid = SysCol.id And SysFrKey.fkey = SysCol.colid )
Inner join syscolumns SysCol2 on ( SysFrKey.rkeyid = SysCol2.id And SysFrKey.rkey = SysCol2.colid )
Order by Master_Table_Name,Child_Table_Name

 

1 Comment

Comments have been disabled for this content.