sp_commonkey

Description

Defines a common key—columns that are frequently joined—between two tables or views.

Syntax

sp_commonkey tabaname, tabbname, col1a, col1b 
	[, col2a, col2b, ..., col8a, col8b]

Parameters

tabaname

is the name of the first table or view to be joined.

tabbname

is the name of the second table or view to be joined.

col1a

is the name of the first column in the table or view tabaname that makes up the common key. Specify at least one pair of columns (one column from the first table or view and one from the second table or view).

col1b

is the name of the partner column in the table or view tabbname that is joined with col1a in the table or view tabaname.

Examples

Example 1

Defines a common key on titles.titleid and titleauthor.titleid:

sp_commonkey titles, titleauthor, title_id, title_id

Example 2

Assumes two tables, projects and departments, each with a column named empid. This statement defines a frequently used join on the two columns:

sp_commonkey projects, departments, empid, empid

Usage

Permissions

You must be the table owner to execute sp_commonkey. Permission checks do not differ based on the granular permissions settings

Auditing

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

Event

Audit option

Command or access audited

Information in extrainfo

38

exec_procedure

Execution of a procedure

  • Roles – Current active roles

  • Keywords or options – NULL

  • Previous value – NULL

  • Current value – NULL

  • Other information – All input parameters

  • Proxy information – Original login name, if set proxy in effect

See also

Commands alter table, create table, create trigger

System procedures sp_dropkey, sp_foreignkey, sp_helpjoins, sp_helpkey, sp_primarykey