Returns column information for the specified tables or views that can be queried in the current environment.
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.
Table or view qualifier name. This field can be NULL.
Table or view owner name. This field always returns a value.
Table or view name. This field always returns a value.
Column name, for each column of the TABLE_NAME returned. This field always returns a value.
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.
String representing a data type. The underlying DBMS presents this data type name.
Number of significant digits. The return value for the PRECISION column is in base 10.
Transfer size of the data.1
Number of digits to the right of the decimal point.
Base for numeric data types.
1 = NULL is possible.
0 = NOT NULL.
This field always returns NULL.
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.
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.
Subtype code for datetime and SQL-92 interval data types. For other data types, this column returns NULL.
Maximum length in bytes of a character or integer data type column. For all other data types, this column returns NULL.
Ordinal position of the column in the table. The first column in the table is 1. This column always returns a value.
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.
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.
sp_columns follows the requirements for delimited identifiers. For more information, see Delimited Identifiers (Database Engine).
The following example returns column information for a specified table.
USE AdventureWorks GO EXEC sp_columns @table_name = N'Department', @table_owner = N'HumanResources';