Determining which table columns to join

sp_helpjoins lists the columns in two tables or views that are likely join candidates. Its syntax is:

sp_helpjoins table1, table2 

For example, here is how to use sp_helpjoins to find the likely join columns between titleauthor and titles:

sp_helpjoins titleauthor, titles 
first_pair                                                    
----------------------------- --------------------
title_id                      title_id 

The column pairs that sp_helpjoins displays come from two sources. First, sp_helpjoins checks the syskeys table in the current database to see if any foreign keys have been defined on the two tables with sp_foreignkey, and then checks to see if any common keys have been defined on the two tables with sp_commonkey. If it does not find any common keys there, the procedure applies less restrictive criteria to identify any keys that may be reasonably joined. It checks for keys with the same user datatypes, and if that fails, for columns with the same name and datatype.

See the Reference Manual: Procedures.