sp_helpjoins

Syntax

sp_helpjoins lefttab, righttab

Parameters

• lefttab – is the first table or view.
• righttab – is the second table or view. The order of the parameters does not matter.

Examples

• Example 1 – Displays a list of columns that are likely join candidates in the sales and salesdetail tables:

  sp_helpjoins sales, salesdetail

  a1       a2       b1       b2       c1       c2
     d1       d2       e1       e2       f1       f2
        g1       g2       h1       h2
  -------- -------- -------- -------- -------- -------- 
  -------- -------- -------- -------- -------- -------- 
        -------- -------- -------- -------- 
  stor_id  stor_id  ord_num  ord_num  NULL     NULL
     NULL     NULL     NULL     NULL     NULL     NULL
        NULL     NULL     NULL     NULL

• Example 2 – Displays a list of columns that are likely join candidates in the sysobjects and syscolumns system tables:

  sp_helpjoins sysobjects, syscolumns

  a1   a2   b1   b2   c1   c2   d1   d2   e1   e2
          f1   f2   g1   g2   h1   h2
  ---- ---- ---- ---- ---- ---- ---- ---- ---- ----
          ---- ---- ---- ---- ---- ----
  id   id   NULL NULL NULL NULL NULL NULL NULL NULL
          NULL NULL NULL NULL NULL NULL

Usage

The column pairs that sp_helpjoins displays come from either of two sources. sp_helpjoins checks the syskeys table in the current database to see if any foreign keys have been defined with sp_foreignkey on the two tables, then checks to see if any common keys have been defined with sp_commonkey on the two tables. If sp_helpjoins does not find any foreign keys or common keys there, it checks for keys with the same user-defined datatypes. If that fails, it checks for columns with the same name and datatype.

sp_helpjoins does not create any joins.

Permissions

Any user can execute sp_helpjoins. Permission checks do not differ based on the granular permissions settings.

Auditing

Values in event and extrainfo columns from the sysaudits table are: