sp_special_columns

Returns the optimal set of columns that uniquely identify a row in a table or view; can also return a list of timestamp columns, with values that are automatically generated when any value in the row is updated by a transaction.

Syntax

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

Parameters

table_name – is the name of the table or view. The use of wildcard characters in pattern matching is not supported.
table_owner – 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.
table_qualifier – is the name of the database. This can be either the current database or NULL.
col_type – 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, with values that are generated by the SAP ASE server each time a row is inserted or updated.

Examples

Example 1 – 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

Example 2 – 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

Usage

The results set for sp_special_columns is:

Column	Datatype	Description
`scope`	`int`	NOT NULL. Actual scope of the row ID. The SAP ASE 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.
`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.

Permissions

Any user can execute sp_special_columns.