sp_special_columns Catalog Stored Procedures

Returns the optimal set of columns that uniquely identify a row in the table and columns that are automatically updated when any value in the row is updated by a transaction.

Syntax

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

where

table_name: Specifies the table used to return catalog information. The table_name can be a variable character length name with a maximum of 32 characters. Wildcard pattern matching is not supported.
table_owner: Specifies the table owner of the table used to return catalog information. The table_owner can be a variable character name with a maximum 32 characters. Wildcard pattern matching is not supported. If the table_owner is not specified, the default table visibility rules of the underlying DBMS apply.
In Microsoft SQL Server, if the current user owns a table with the specified name, that table's columns are returned. If the table_owner is not specified and the current user does not own a table with the specified table_name, this procedure will look for a table with the specified table_name owned by the database owner. If one exists, that table's columns are returned.
table_qualifier: Is the name of the table qualifier. Various DBMS products support three-part naming for tables (qualifier.owner.name). In Microsoft SQL Server, this column represents the database name. In some products, it represents the server name of the table's database environment.
col_type: Specifies the column type, either R or V. Type R returns the optimal column or set of columns that, by retrieving values from the column or columns, allows any row in the specified table to be uniquely identified. A column can be either a pseudo column specifically designed for this purpose, or the column or columns of any unique index for the table. Type V returns the column or columns in the specified table, if any, that are automatically updated by the data source when any value in the row is updated by any transaction.
scope: Specifies the minimum required scope of the ROWID, either C or T. Scope C specifies that the ROWID is valid only when positioned on that row. Scope T specifies that the ROWID is valid for the transaction.
nullable: Specifies whether or not the special column can accept a null value, either U or O. Nullable U specifies that this special column allows null values. Nullable O is a special column that does not allow null values.

Remarks

The sp_special_columns stored procedure is equivalent to SQLSpecialColumns in ODBC. The results returned are ordered by SCOPE.

This is the results set:



Column	Datatype	Description

SCOPE	int	Is the actual scope of the row ID. Can be 0, 1, or 2. SQL Server always returns 0. This field will always return a value. 0 SQL_SCOPE_CURROW. The row ID is guaranteed to be valid only while positioned on that row. A later reselect using the row ID may not return a row if the row was updated or deleted by another transaction. 1 SQL_SCOPE_TRANSACTION. The row ID is guaranteed to be valid for the duration of the current transaction. 2 SQL_SCOPE_SESSION. The row id is guaranteed to be valid for the duration of the session (across transaction boundaries).
COLUMN_NAME	varchar(32)	Is the name of the column, for each column of the TABLE_NAME returned. In Microsoft SQL Server, this column represents the column name as listed in the syscolumns table. This field will always return a value.
DATA_TYPE	smallint	Is an ODBC SQL datatype.
TYPE_NAME	varchar(13)	Is a data source-dependent datatype name; for example, 'CHAR', 'VARCHAR', 'MONEY', or 'TEXT'.
PRECISION	int	Is the precision of the column on the data source. This field will always return a value.
LENGTH	int	Is the length, in bytes, required for the datatype in its binary form in the data source. For example, 10 for CHAR(10), 4 for INTEGER, and 2 for SMALLINT.
SCALE	smallint	Is the scale of the column on the data source. NULL is returned for datatypes where scale is not applicable.
PSEUDO_COLUMN	smallint	Indicates whether the column is a pseudo column. SQL Server 6.0 always returns 2. Can be: 0 SQL_PC_UNKNOWN 1 SQL_PC_PSEUDO 2 SQL_PC_NOT_PSEUDO