MSSQL Tip: Get Connected Columns in Related Tables

How to get Foreign Key related columns, having the names of Primary and Foreign tables ? 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 [name] 
  FROM syscolumns 
 WHERE [id] IN (SELECT [id] 
                  FROM sysobjects 
                 WHERE [name] = @foreign_table_name) 
   AND colid IN (SELECT fkey
                   FROM sysforeignkeys SFK 
                   JOIN sysobjects SOF ON SFK.fkeyid = SOF.[id] 
                   JOIN sysobjects SOP ON SFK.rkeyid = SOP.[id]
                  WHERE SOF.[name] = @foreign_table_name
                    AND SOP.[name] = @primary_table_name)

Related Posts:

• 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