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

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 9-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.

Permissions

Any user can execute sp_special_columns.