TechNet
Export (0) Print
Expand All

sp_columns (Transact-SQL)

 

THIS TOPIC APPLIES TO: yesSQL Server (starting with 2008)yesAzure SQL DatabaseyesAzure SQL Data Warehouse yesParallel Data Warehouse

Returns column information for the specified objects that can be queried in the current environment.

Topic link icon Transact-SQL Syntax Conventions

-- Syntax for SQL Server, Azure SQL Database, Azure SQL Data Warehouse, Parallel Data Warehouse  
  
sp_columns [ @table_name = ] object  
     [ , [ @table_owner = ] owner ]   
     [ , [ @table_qualifier = ] qualifier ]   
     [ , [ @column_name = ] column ]   
     [ , [ @ODBCVer = ] ODBCVer ]  

[ @table_name=] object
Is the name of the object that is used to return catalog information. object can be a table, view, or other object that has columns such as table-valued functions. object is nvarchar(384), with no default. Wildcard pattern matching is supported.

[ @table_owner****=] owner
Is the object owner of the object 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 object visibility rules of the underlying DBMS apply.

If the current user owns an object with the specified name, the columns of that object are returned. If owner is not specified and the current user does not own an object with the specified object, sp_columns looks for an object with the specified object owned by the database owner. If one exists, that object's columns are returned.

[ @table_qualifier****=] qualifier
Is the name of the object qualifier. qualifier is sysname, with a default of NULL. Various DBMS products support three-part naming for objects (qualifier.owner.name). In SQL Server, this column represents the database name. In some products, it represents the server name of the object'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.

None

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 nameData typeDescription
TABLE_QUALIFIERsysnameObject qualifier name. This field can be NULL.
TABLE_OWNERsysnameObject owner name. This field always returns a value.
TABLE_NAMEsysnameObject name. This field always returns a value.
COLUMN_NAMEsysnameColumn name, for each column of the TABLE_NAME returned. This field always returns a value.
DATA_TYPEsmallintInteger 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_NAMEsysnameString representing a data type. The underlying DBMS presents this data type name.
PRECISIONintNumber of significant digits. The return value for the PRECISION column is in base 10.
LENGTHintTransfer size of the data.1
SCALEsmallintNumber of digits to the right of the decimal point.
RADIXsmallintBase for numeric data types.
NULLABLEsmallintSpecifies nullability.

1 = NULL is possible.

0 = NOT NULL.
REMARKSvarchar(254)This field always returns NULL.
COLUMN_DEFnvarchar(4000)Default value of the column.
SQL_DATA_TYPEsmallintValue 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_SUBsmallintSubtype code for datetime and SQL-92 interval data types. For other data types, this column returns NULL.
CHAR_OCTET_LENGTHintMaximum length in bytes of a character or integer data type column. For all other data types, this column returns NULL.
ORDINAL_POSITIONintOrdinal position of the column in the object. The first column in the object is 1. This column always returns a value.
IS_NULLABLEvarchar(254)Nullability of the column in the object. 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_TYPEtinyintSQL 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.

Requires SELECT permission on the schema.

sp_columns follows the requirements for delimited identifiers. For more information, see Database Identifiers.

The following example returns column information for a specified table.

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

The following example returns column information for a specified table.

-- Uses AdventureWorks  
  
EXEC sp_columns @table_name = N'DimEmployee',  
   @table_owner = N'dbo';  

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

Community Additions

ADD
Show:
© 2016 Microsoft