Partilhar via

sp_special_columns (Transact-SQL)

  • Artigo

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

Topic link iconTransact-SQL Syntax Conventions

Syntax

sp_special_columns [@table_name =] 'table_name'   
     [,[@table_owner =] 'table_owner'] 
     [,[@qualifier =] 'qualifier'] 
     [,[@col_type =] 'col_type'] 
     [,[@scope =] 'scope']
     [,[@nullable =] 'nullable'] 
     [,[@ODBCVer =] 'ODBCVer'] ;

Arguments

  • [ @table_name =] 'table_name'
    Is the name of the table used to return catalog information. name is sysname, with no default. Wildcard pattern matching is not supported.

  • [ @table_owner =] 'table_owner'
    Is the table owner of the table used to return catalog information. owner is sysname, with a default of NULL. Wildcard pattern matching is not supported. If owner is not specified, the default table visibility rules of the underlying DBMS apply.

    In SQL Server, if the current user owns a table with the specified name, the columns of that table are returned. If owner is not specified and the current user does not own a table of the specified name, this procedure looks for a table of the specified name owned by the database owner. If the table exists, its columns are returned.

  • [ @qualifier =] 'qualifier'
    Is the name of the table qualifier. qualifier is sysname, with a default of NULL. Various DBMS products support three-part naming for tables (qualifier.owner.name). In SQL Server, this column represents the database name. In some products, it represents the server name of the database environment of the table.
  • [ @col_type =] 'col_type'
    Is the column type. col_type is char(1), with a default of R. Type R returns the optimal column or set of columns that, by retrieving values from the column or columns, allows for any row in the specified table to be uniquely identified. A column can be either a pseudocolumn 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 =] 'scope'
    Is the minimum required scope of the ROWID. scope is char(1), with a default of 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 =] 'nullable'
    Is whether the special columns can accept a null value. nullable is char(1), with a default of U. O specifies special columns that do not allow null values. U specifies columns that are partially nullable.
  • [ @ODBCVer =] 'ODBCVer'
    Is the ODBC version being used. ODBCVer is int(4), with a default of 2. This indicates ODBC version 2.0. For more information about the difference between ODBC version 2.0 and ODBC version 3.0, see the ODBC SQLSpecialColumns specification for ODBC version 3.0.

Return Code Values

None

Result Sets

Column name Data type Description

SCOPE

smallint

Actual scope of the row ID. Can be 0, 1, or 2. SQL Server always returns 0. This field always returns 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

sysname

Column name for each column of the tablereturned. This field always returns a value.

DATA_TYPE

smallint

ODBC SQL data type.

TYPE_NAME

sysname

Data source-dependent data type name; for example, char, varchar, money, or text.

PRECISION

Int

Precision of the column on the data source. This field always returns a value.

LENGTH

Int

Length, in bytes, required for the data type in its binary form in the data source, for example, 10 for char(10), 4 for integer, and 2 for smallint.

SCALE

smallint

Scale of the column on the data source. NULL is returned for data types for which scale is not applicable.

PSEUDO_COLUMN

smallint

Indicates whether the column is a pseudocolumn. SQL Server always returns 1:

0 = SQL_PC_UNKNOWN

1 = SQL_PC_NOT_PSEUDO

2 = SQL_PC_PSEUDO

Remarks

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

Permissions

Requires SELECT permission on the schema.

Examples

The following example returns information about the column that uniquely identifies rows in the HumanResources.Department table.

USE AdventureWorks;
GO
EXEC sp_special_columns @table_name = 'Department', 
   @table_owner = 'HumanResources'

See Also

Reference

Catalog Stored Procedures (Transact-SQL)
System Stored Procedures (Transact-SQL)

Help and Information

Getting SQL Server 2005 Assistance

Change History

Release History

14 April 2006
Changed content:
  • Corrected PSEUDO_COLUMN values.