Returns column information for the specified tables or views that can be queried in the current environment.
sp_columns [@table_name =] object
[,[@table_owner =] owner]
[,[@table_qualifier =] qualifier]
[,[@column_name =] column]
[,[@ODBCVer =] ODBCVer]
In Microsoft® SQL Server™, if the current user owns a table or view with the specified name, that table’s columns 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.
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 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 | varchar(13) | 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. (*) |
SCALE | smallint | Number of digits to the right of the decimal point. |
RADIX | smallint | Base for numeric datatypes. |
NULLABLE | smallint | Specifies nullability. 1 = NULL is possible. 0 = NOT NULL. |
REMARKS | varchar(254) | This field always returns NULL. |
COLUMN_DEF | varchar(254) | Default value of the column. |
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. 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 Open Data Services extended stored procedures. For more information, see Data Types. |
* For more information about length, see the Microsoft ODBC Programmer’s Reference and SDK Guide, available separately. |
Execute permission defaults to the public role.
This example returns column information for a specified table.
EXEC sp_columns @table_name = 'customers'
sp_tables | System Stored Procedures |