sp_columns (Transact-SQL)

sp_columns (Transact-SQL)

Switch on low bandwidth view

Community Content

In this section

Statistics Annotations (0)

Click here to expand

Collapse All

Language Filter

Other versions are also available for the following:

SQL Server 2005

SQL Server 2008 Books Online (June 2009)

sp_columns (Transact-SQL)

Returns column information for the specified tables or views that can be queried in the current environment.

Topic link icon Transact-SQL Syntax Conventions

Syntax

sp_columns [ @table_name = ] object  [ , [ @table_owner = ] owner ] 
     [ , [ @table_qualifier = ] qualifier ] 
     [ , [ @column_name = ] column ] 
     [ , [ @ODBCVer = ] ODBCVer ]

Arguments

[ @table_name =] object: Is the name of the table or view that is used to return catalog information. object_name is nvarchar(384), with no default. Wildcard pattern matching is supported.

[ @table_owner =] owner

Is the object owner of the table or view that is used to return catalog information. owner is nvarchar(384), with a default of NULL. Wildcard pattern matching is supported. If owner is not specified, the default table or view visibility rules of the underlying DBMS apply.

If the current user owns a table or view 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 or view with the specified object, sp_columns looks for a table or view with the specified object owned by the database owner. If one exists, that table's columns are returned.

[ @table_qualifier =] qualifier: Is the name of the table or view 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 table's database environment.

[ @column_name =] column: Is a single column and is used when only one column of catalog information is wanted. column is nvarchar(384), with a default of NULL. If column is not specified, all columns are returned. In SQL Server, column represents the column name as listed in the syscolumns table. Wildcard pattern matching is supported. For maximum interoperability, the gateway client should assume only SQL-92 standard pattern matching (the % and _ wildcard characters).

[ @ODBCVer =] ODBCVer: Is the version of ODBC that is being used. ODBCVer is int, with a default of 2. This indicates ODBC Version 2. Valid values are 2 or 3. For the behavior differences between versions 2 and 3, see the ODBC SQLColumns specification.

Return Code Values

None

Result Sets

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.

Column name	Data type	Description
TABLE_QUALIFIER	sysname	Table or view qualifier name. This field can be NULL.
TABLE_OWNER	sysname	Table or view owner name. This field always returns a value.
TABLE_NAME	sysname	Table or view name. This field always returns a value.
COLUMN_NAME	sysname	Column name, for each column of the TABLE_NAME returned. This field always returns a value.
DATA_TYPE	smallint	Integer code for ODBC data type. If this is a data type that cannot be mapped to an ODBC type, it is NULL. The native data type name is returned in the TYPE_NAME column.
TYPE_NAME	sysname	String representing a data type. The underlying DBMS presents this data type name.
PRECISION	int	Number of significant digits. The return value for the PRECISION column is in base 10.
LENGTH	int	Transfer size of the data.1
SCALE	smallint	Number of digits to the right of the decimal point.
RADIX	smallint	Base for numeric data types.
NULLABLE	smallint	Specifies nullability. 1 = NULL is possible. 0 = NOT NULL.
REMARKS	varchar(254)	This field always returns NULL.
COLUMN_DEF	nvarchar(4000)	Default value of the column. SQL Server 2008 and SQL Server 2005 differ from SQL Server 2000 in the way they decode and stores SQL expressions in the catalog metadata. The semantics of the decoded expression are equivalent to the original text; however, there are no syntactic guarantees. For example, white spaces are removed from the decoded expression. For more information, see, Behavior Changes to Database Engine Features in SQL Server 2008.
SQL_DATA_TYPE	smallint	Value of the SQL data type as it appears in the TYPE field of the descriptor. This column is the same as the DATA_TYPE column, except for the datetime and SQL-92 interval data types. This column always returns a value.
SQL_DATETIME_SUB	smallint	Subtype code for datetime and SQL-92 interval data types. For other data types, this column returns NULL.
CHAR_OCTET_LENGTH	int	Maximum length in bytes of a character or integer data type column. For all other data types, this column returns NULL.
ORDINAL_POSITION	int	Ordinal position of the column in the table. The first column in the table is 1. This column always returns a value.
IS_NULLABLE	varchar(254)	Nullability of the column in the table. ISO rules are followed to determine nullability. An ISO SQL-compliant DBMS cannot return an empty string. YES = Column can include NULLS. NO = Column cannot include NULLS. This column returns a zero-length string if nullability is unknown. The value returned for this column is different from the value returned for the NULLABLE column.
SS_DATA_TYPE	tinyint	SQL Server data type used by extended stored procedures. For more information, see Data Types (Transact-SQL).

1 For more information, see the Microsoft ODBC documentation.

Permissions

Requires SELECT permission on the schema.

Examples

The following example returns column information for a specified table.

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

Reference

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

Help and Information

Getting SQL Server 2008 Assistance