sp_columns Catalog Stored Procedure

For SQL Server 6.5 information, see sp_columns in What's New for SQL Server 6.5.

Returns column information for a single object that can be queried in the current environment. The returned columns belong to a table or a view.

Syntax

sp_columns object_name [, object_owner] [, object_qualifier] [, column_name]

where

object_name: Is the name of the table or view used to return catalog information. The object_name can be a variable character name with a maximum of 32 characters. Wildcard pattern matching is not supported.
object_owner: Specifies the object owner of the table or view used to return catalog information. The object_owner can be a variable character name with a maximum of 32 characters. Wildcard pattern matching is not supported. If object_owner is not specified, the default table or view visibility rules of the underlying DBMS apply.
In Microsoft SQL Server, if the current user owns a table or view with the specified name, that table's columns are returned. If object_owner is not specified and the current user does not own a table or view with the specified object_name, this procedure looks for a table or view with the specified object_name owned by the database owner. If one exists, that table's columns are returned.
object_qualifier: Specifies the name of the table or view 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.
column_name: Specifies a single column and is used when only one column of catalog information is desired. If column_name is not specified, all columns will be returned. In Microsoft SQL Server, this column_name represents the column name as listed in the syscolumns table. The value specified can include wildcard characters using the underlying DBMS's wildcard matching patterns. For maximum interoperability, the gateway client should assume only ANSI-standard SQL pattern matching (the % and _ wildcard characters).

Remarks

The sp_columns catalog stored procedure is equivalent to SQLColumns in ODBC. The results returned are ordered by TABLE_QUALIFIER, TABLE_OWNER, and TABLE_NAME.

This is the results set:



Column	Datatype	Description

TABLE_QUALIFIER	varchar(32)	Is the name of the table or view 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. This field can be NULL.
TABLE_OWNER	varchar(32)	Is the name of the table or view owner. Various DBMS products support three-part naming for tables (qualifier.owner.name). In Microsoft SQL Server, this column represents the name of the database user that created the table. This field will always return a value.
TABLE_NAME	varchar(32)	Is the name of the table or view. Various DBMS products support three-part naming for tables (qualifier.owner.name). In Microsoft SQL Server, this column represents the table or view name as listed in the sysobjects table. This field will always return a value.
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 integer code for ODBC datatype. If this is a datatype that cannot be mapped to an ODBC type, it is NULL. The native datatype name is returned in the TYPE_NAME column.
TYPE_NAME	varchar(13)	Is a string representing a datatype. The underlying DBMS presents this datatype name.
PRECISION	int	Is the number of significant digits.
LENGTH	int	Is the transfer size of the data. (*)
SCALE	smallint	Is the number of digits to the right of the decimal point.
RADIX	smallint	Is the base for numeric types.
NULLABLE	smallint	Specifies nullability. 1 means NULL is possible; 0 means NOT NULL.
REMARKS	varchar(254)	For Microsoft SQL Server, this field will always return NULL.
SS_DATA_TYPE	tinyint	Is a SQL Server datatype, as defined in Microsoft SQL Server Programming Open Data Services, used by ODS gateway applications. The gateway converts the underlying data to this SQL Server datatype as the default. If the gateway supports configurable datatype conversions, either per installation or per session, then the conversion type is returned when the stored procedure is executed.
COLID	tinyint	Is a SQL Server - specific column appended to the result set. This column does not need to appear in Open Data Services gateway implementations of catalog stored procedures.
* For details about length, see the Microsoft ODBC Programmer's Reference and SDK Guide, available separately.