sp_special_columns

Retrieves the following information about columns within a specified table or view:

• The optimal set of columns that uniquely identify a row in the table or view

• A list of the columns that are automatically updated when any value in the row is updated

sp_special_columns table_name [, table_owner] [, table_qualifier] [, col_type]

table_name is the name of the table. Views, aliases, and wildcard-character search patterns are not supported.

table_owner is the owner of the table. Wildcard-character search patterns are not supported. If you do not specify this parameter, sp_special_columns looks first for a table owned by the current user and then for a table owned by the database owner.

table_qualifier is ignored. Leave blank or set to NULL.

col_type is a value that requests information about columns of a specific type as follows:

• R returns information about columns with values that uniquely identify any row in the table.

• V returns information about columns with values that are automatically generated by a target each time a row is inserted or updated.

• This function corresponds to the ODBC function SQLSpecialColumns.

• Information is based on the SYSINDEXES, SYSKEYS, and SYSCOLUMNS system catalog tables.

sp_special_columns returns information about the columns that uniquely identify a row in a table.

The result set consists of a row for each column of an index that uniquely identifies each row of the table. If there are multiple unique indexes on a table, the one that is described by the result set is the first that exists in the following list:

• A primary key with clustered index

• A primary key without clustered index

• A unique, clustered index

• A unique, non-clustered index

The result set is ordered by the column name in the index.

The lengths for varchar columns shown in the result set tables are maximums; the actual lengths depend on the target database.