Determine Which Table Columns to Join

Use sp_helpjoins to obtain a list of the columns in two tables or views that are likely join candidates.

The syntax is:

sp_helpjoins table1, table2

For example, to find the likely join columns between titleauthor and titles, use:

sp_helpjoins titleauthor, titles

first_pair                                                    
----------------------------- --------------------
title_id                       title_id

The column pairs that sp_helpjoins shows 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.