MSSQL Tip: Get Primary Table in Relationship

How to get Primary Table, having the name of Foreign table in relation ? We can use the following MSSQL system tables:

• sysobjects - for all user objects.
• sysforeignkeys - for foreign keys.
• syscolumns - for tables columns.

Build the following query:

SELECT SOP.[name] 
  FROM sysforeignkeys SFK 
  JOIN sysobjects SOF ON (SFK.fkeyid = SOF.[id]) 
  JOIN sysobjects SOP ON (SFK.rkeyid = SOP.[id]) 
  JOIN syscolumns C ON (C.[id] = SOF.[id] AND C.colid = SFK.fkey)  
 WHERE SOF.[name] = @foreign_table_name 
   AND C.[name] = @foreign_col_name 

Pay attention how sysobjects is used twice in a query.

Related Posts:

• MSSQL Tip: Get Table Primary Key Column(s)
• ROWNUM [ PL/SQL ] Analog for T-SQL
• MSSQL Tip: Get Connected Columns in Related Tables
• How To Shrink Transaction Log in MSSQL 2000
• T-SQL: Output Parameter in sp_executesql
• MSSQL: Change tables owner to dbo with sp_changeobjectowner
• MSSQL: Recompile Stored Procedures, Views, Functions
• MSSQL Tip: Rebuild Clustered Indexes
• T-SQL: Aggregate Values Multiplication Like SUM Aggregates Values Sum
• SQL Tip: HowTo Convert Delimited String to Table