sp_columns
Returns column information for the specified tables or views that can be queried in the current environment.
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 used to return catalog information. object_name is nvarchar(384), with no default. Wildcard pattern matching is not supported.
[@table_owner =] owner
Is the object owner of the table or view used to return catalog information. owner is nvarchar(384), with a default of NULL. Wildcard pattern matching is not supported. If 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 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. column can include wildcard characters using the underlying DBMS's wildcard matching patterns. 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 being used. ODBCVer is int, with a default of 2, indicating ODBC Version 2. Valid values are 2 or 3. Refer to the ODBC SQLColumns specification for the behavior differences between versions 2 and 3.
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 | 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.1 |
| 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. |
| REMARKS | varchar(254) | This field always returns NULL. |
| COLUMN_DEF | nvarchar(4000) | 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, see the Microsoft ODBC documentation.
Permissions
Execute permission defaults to the public role.
Examples
This example returns column information for a specified table.
EXEC sp_columns @table_name = 'customers'