Returns the optimal set of columns that uniquely identify a row in a table or view; can also return a list of timestamp columns, whose values are automatically generated when any value in the row is updated by a transaction.
sp_special_columns table_name [, table_owner] [, table_qualifier] [, col_type]
is the name of the table or view. The use of wildcard characters in pattern matching is not supported.
is the name of the table or view owner. The use of wildcard characters in pattern matching is not supported. If you do not specify the table owner, sp_special_columns looks for a table owned by the current user and then for a table owned by the Database Owner.
is the name of the database. This can be either the current database or NULL.
is “R” to return information about columns with values that uniquely identify any row in the table, or “V” to return information about timestamp columns, whose values are generated by Adaptive Server each time a row is inserted or updated.
Returns the optimal set of columns for systypes:
sp_special_columns systypes
scope column_name data_type type_name precision length scale ------ ------------ ---------- ---------- ---------- ------- ------ 0 name 12 varchar 30 30 NULL
Returns the optimal set from the from the authors table with values that uniquely identify any row in the table:
sp_special_columns @table_name=authors, @col_type=R
scope column_name data_type type_name precision length scale ------ ------------ ---------- ---------- ---------- ------- ------ 0 au_id 12 varchar 11 11 NULL
The results set for sp_special_columns is:
Column |
Datatype |
Description |
|---|---|---|
scope |
int |
NOT NULL. Actual scope of the row ID. Adaptive Server always returns 0. |
column_name |
varchar(30) |
NOT NULL. Column identifier. |
data_type |
smallint |
The integer code for an ODBC datatype. If this datatype cannot be mapped to an ANSI/ISO type, the value is NULL. The native datatype name is returned in the type_name column. (See the ODBC datatypes Table 2-2.) |
type_name |
varchar(13) |
The string representation of the datatype. This is the datatype name as presented by the underlying DBMS. |
precision |
int |
The number of significant digits. |
length |
int |
The length in bytes of the datatype. |
scale |
smallint |
The number of digits to the right of the decimal point. |
Any user can execute sp_special_columns.
