Find Table Relationships script

This script is a T-SQL script for Microsoft SQL Server that displays the relationships between tables in a database. It does this by querying the system catalog views sys.sysforeignkeys and sys.syscolumns to get information about the foreign keys in the database.

The script starts by declaring a variable @table with a default value of 'TableName'. This variable can be changed to the name of any table in the database to get the relationships for that specific table.

The script then uses the SELECT statement to retrieve the following information:

  • The name of the parent table (Parent_Table)
  • The name of the child table (Child_Table)
  • The name of the foreign key constraint (FKey_Name)
  • The name of the column in the parent table that the foreign key references (Parent_Table_Column)
  • The name of the column in the child table that is the foreign key (Child_Table_Column)

The information is retrieved by joining the system catalog views sys.sysforeignkeys and sys.syscolumns on the foreign key and reference key columns. The result set is filtered by the parent table name, which is passed in as the value of the @table variable.

Finally, the result set is ordered by the parent table and child table names.

To use this script, simply replace the value of the @table variable with the name of the table for which you want to retrieve relationships. Then run the script in a query window in SQL Server Management Studio or a similar tool.

Declare @table varchar(50) = 'TableName'

Select
   object_name(rkeyid) Parent_Table,
   object_name(fkeyid) Child_Table,
   object_name(constid) FKey_Name,
   c2.name Parent_Table_Column,--Ref_KeyCol,
   c1.name Child_Table_Column--FKey_Col
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 )
where object_name(rkeyid) = @table 
   --or object_name(fkeyid) = @table -- uncomment line to include parent database relationships
Order by Parent_Table,Child_Table