Skip to content

sp-primarykeys-transact-sql.md

Latest commit

 

History

History
103 lines (75 loc) · 3.95 KB

sp-primarykeys-transact-sql.md

File metadata and controls

103 lines (75 loc) · 3.95 KB
title sp_primarykeys (Transact-SQL)
description sp_primarykeys returns the primary key columns, one row per key column, for the specified remote table.
author markingmyname
ms.author maghan
ms.reviewer randolphwest
ms.date 06/23/2025
ms.service sql
ms.subservice system-objects
ms.topic reference
f1_keywords
sp_primarykeys_TSQL
sp_primarykeys
helpviewer_keywords
sp_primarykeys
dev_langs
TSQL

sp_primarykeys (Transact-SQL)

[!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

Syntax

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

Arguments

[ @table_server = ] N'table_server'

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

[ @table_name = ] N'table_name'

The name of the table for which to provide primary key information. @table_name is sysname, with a default of NULL.

[ @table_schema = ] N'table_schema'

The table schema. @table_schema is sysname, with a default of NULL. In the [!INCLUDE ssNoVersion] environment, this value corresponds to the table owner.

[ @table_catalog = ] N'table_catalog'

The name of the catalog in which the specified @table_name resides. @table_catalog is sysname, with a default of NULL. In the [!INCLUDE ssNoVersion] environment, this value corresponds to the database name.

Return code values

None.

Result set

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.

Remarks

sp_primarykeys is executed by querying the PRIMARY_KEYS rowset of the IDBSchemaRowset interface of the OLE DB provider corresponding to @table_server. The 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 doesn't support the PRIMARY_KEYS rowset of the IDBSchemaRowset interface.

Permissions

Requires SELECT permission on the schema.

Examples

The following example returns primary key columns from the LONDON1 server for the HumanResources.JobCandidate table in the [!INCLUDE ssSampleDBobject] database.

EXECUTE sp_primarykeys
    @table_server = N'LONDON1',
    @table_name = N'JobCandidate',
    @table_catalog = N'AdventureWorks2022',
    @table_schema = N'HumanResources';

Related content