title | description | author | ms.author | ms.date | ms.service | ms.subservice | ms.topic | f1_keywords | helpviewer_keywords | dev_langs | ||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
sp_primarykeys (Transact-SQL) |
sp_primarykeys (Transact-SQL) |
markingmyname |
maghan |
03/14/2017 |
sql |
system-objects |
reference |
|
|
|
[!INCLUDE SQL Server]
Returns the primary key columns, one row per key column, for the specified remote table.
:::image type="icon" source="../../includes/media/topic-link-icon.svg" border="false"::: 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 [!INCLUDEssNoVersion] 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 [!INCLUDEssNoVersion] environment, this corresponds to the database name. table_catalog is sysname, with a default of NULL.
None
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 [!INCLUDEssSampleDBobject] database.
EXEC sp_primarykeys @table_server = N'LONDON1',
@table_name = N'JobCandidate',
@table_catalog = N'AdventureWorks2022',
@table_schema = N'HumanResources';
Distributed Queries Stored Procedures (Transact-SQL)
sp_catalogs (Transact-SQL)
sp_column_privileges (Transact-SQL)
sp_foreignkeys (Transact-SQL)
sp_indexes (Transact-SQL)
sp_linkedservers (Transact-SQL)
sp_tables_ex (Transact-SQL)
sp_table_privileges (Transact-SQL)
System Stored Procedures (Transact-SQL)