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]
is the name of the table. Views, aliases, and wildcard-character search patterns are not supported.
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.
is ignored. Leave blank or set to NULL.
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.
Table 4-14 shows the result set for sp_special_columns.
Column |
Datatype |
Description |
|---|---|---|
SCOPE |
smallint NOT NULL |
Actual scope of the row ID:
|
COLUMN_NAME |
varchar(128) NOT NULL |
Column name |
DATA_TYPE |
smallint NOT NULL |
ODBC datatype to which all columns of this type are mapped |
TYPE_NAME |
varchar(128) NOT NULL |
Name of the target database datatype that corresponds to the ODBC datatype in the DATA_TYPE column |
PRECISION |
int |
Maximum precision for the datatype in the target database; NULL if precision is not applicable |
LENGTH |
int |
Length of the column in bytes |
SCALE |
smallint |
Number of digits to the right of the decimal point; NULL if scale is not applicable |
PSEUDO_COLUMN |
smallint |
Indicates whether the column is a pseudo-column; the access service always returns 0 SQL_PC_UNKNOWN |
