sp_special_columns

Description

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.

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 whose values 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.

Examples

Example 1

sp_special_columns systypes
scope  column_name  data_type  type_name  precision  length  scale  
------ ------------ ---------- ---------- ---------- ------- ------ 
   0   name          12        varchar         30       30   NULL

Example 2

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

Permissions

Any user can execute sp_special_columns.