sp_pkeys Catalog Stored Procedure

Returns primary key information for a single table in the current environment.

Syntax

sp_pkeys table_name [, table_owner] [, table_qualifier]

where

table_name
Specifies the table used to return catalog information. The table_name can be a variable-length character name with a maximum of 32 characters. Wildcard pattern matching is not supported.
table_owner
Specifies the table owner of the table used to return catalog information. The table_owner can be a variable character name with a maximum of 32 characters. Wildcard pattern matching is not supported. If the table_owner is not specified the default table visibility rules of the underlying DBMS apply.

In Microsoft SQL Server, if the current user owns a table with the specified name, that table's columns are returned. If the table_owner is not specified and the current user does not own a table with the specified table_name, this procedure will look for a table with the specified table_name owned by the database owner. If one exists, that table's columns are returned.

table_qualifier
Is the name of the table qualifier. Various DBMS products support three-part naming for tables (qualifier.owner.name). In Microsoft SQL Server, this column represents the database name. In some products, it represents the server name of the table's database environment.

Remarks

In SQL Server 6.0, sp_pkeys returns information about columns explicitly defined with a PRIMARY KEY constraint. Because not all systems support explicitly named primary keys, the gateway implementor determines what constitutes a primary key. Note that the term "primary key" refers to a logical primary key for a table. It is expected that every key listed as being a logical primary key has a unique index defined on it and that this unique index is also returned in sp_statistics.

The sp_pkeys stored procedure is equivalent to SQLPrimaryKeys in ODBC. The results returned are ordered by TABLE_QUALIFIER, TABLE_OWNER, TABLE_NAME, and KEY_SEQ.

This is the results set:

Column Datatype Description
TABLE_QUALIFIER varchar(32) Is the name of the table qualifier. Various DBMS products support three-part naming for tables (qualifier.owner.name). In Microsoft SQL Server, this column represents the database name. In some products, it represents the server name of the table's database environment. This field can be NULL.
TABLE_OWNER varchar(32) Is the name of the table owner. Various DBMS products support three-part naming for tables (qualifier.owner.name). In Microsoft SQL Server, this column represents the name of the database user who created the table. This field will always return a value.
TABLE_NAME varchar(32) Is the name of the table. Various DBMS products support three-part naming for tables (qualifier.owner.name). In Microsoft SQL Server, this column represents the table name as listed in the sysobjects table. This field will always return a value.
COLUMN_NAME varchar(32) Is the name of the column, for each column of the TABLE_NAME returned. In Microsoft SQL Server, this column represents the column name as listed in the syscolumns table. This field will always return a value.
KEY_SEQ smallint Is the sequence number of the column in a multicolumn primary key. NOT NULL.
PK_NAME varchar(32) Is a primary key identifier. Returns NULL if not applicable to the data source.