Export (0) Print
Expand All

sp_primarykeys (Transact-SQL)

Returns the primary key columns, one row per key column, for the specified remote table.

Applies to: SQL Server (SQL Server 2008 through current version).

Topic link icon Transact-SQL Syntax Conventions

sp_primarykeys [ @table_server = ] 'table_server' 
     [ , [ @table_name = ] 'table_name' ] 
     [ , [ @table_schema = ] 'table_schema' ] 
     [ , [ @table_catalog = ] 'table_catalog' ]

[ @table_server = ] 'table_server'

Is the name of the linked server from which to return primary key information. table_server is sysname, with no default.

[ @table_name = ] 'table_name'

Is the name of the table for which to provide primary key information. table_nameis sysname, with a default of NULL.

[ @table_schema = ] 'table_schema'

Is the table schema. table_schema is sysname, with a default of NULL. In the SQL Server environment, this corresponds to the table owner.

[ @table_catalog = ] 'table_catalog'

Is the name of the catalog in which the specified table_name resides. In the SQL Server environment, this corresponds to the database name. table_catalog is sysname, with a default of NULL.

Column name

Data type

Description

TABLE_CAT

sysname

Table catalog.

TABLE_SCHEM

sysname

Table schema.

TABLE_NAME

sysname

Name of the table.

COLUMN_NAME

sysname

Name of the column.

KEY_SEQ

int

Sequence number of the column in a multicolumn primary key.

PK_NAME

sysname

Primary key identifier. Returns NULL if not applicable to the data source.

sp_primarykeys is executed by querying the PRIMARY_KEYS rowset of the IDBSchemaRowset interface of the OLE DB provider corresponding to table_server. The table_name, table_schema, table_catalog, and column parameters are passed to this interface to restrict the rows returned.

sp_primarykeys returns an empty result set if the OLE DB provider of the specified linked server does not support the PRIMARY_KEYS rowset of the IDBSchemaRowset interface.

Requires SELECT permission on the schema.

The following example returns primary key columns from the LONDON1 server for the HumanResources.JobCandidate table in the AdventureWorks2012 database.

EXEC sp_primarykeys @table_server = N'LONDON1', 
   @table_name = N'JobCandidate',
   @table_catalog = N'AdventureWorks2012', 
   @table_schema = N'HumanResources';
Was this page helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

ADD
Show:
© 2014 Microsoft