Returns primary key information for a single table in the current environment.
sp_pkeys table_name [, table_owner] [, table_qualifier]
where
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.
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. |