sp_columns

Returns information about the type of data that can be stored in one or more columns.

Syntax

sp_columns table_name [, table_owner ]
	[, table_qualifier] [, column_name]

Parameters

table_name – is the name of the table or view. Use wildcard characters to request information about more than one table.
table_owner – is the owner of the table or view. Use wildcard characters to request information about tables owned by more than one user. If you do not specify a table owner, sp_columns looks for tables owned by the current user and then for tables owned by the Database Owner.
table_qualifier – is the name of the database. This can be either the current database or NULL.
column_name – is the name of the column for which you want information. Use wildcard characters to request information about more than one column.

Examples

Example 1 – Displays information about all columns in the publishers table that begin with “p”:

sp_columns "publishers", null, null, "p%"

table_qualifier  table_owner  table_name  column_name data_type type_name   precision  length  scale  radix  nullable  remarks ss_data_type colid
---------------- -----------  ----------- ----------- --------- ----------   ---------   ------  -----  -----   --------  ------- ------------ ----
pubs2            dbo         publishers   pub_id       1       char
  NULL       4       NULL   NULL    0        NULL    47           1 
pubs2            dbo         publishers   pub_name     12      varchar
  NULL      40       NULL   NULL    1        NULL    39           2

Example 2 – Displays information about all columns beginning with “st” in tables that begin with “s”:
```
sp_columns "s%", null, null, "st%"
```

Usage

The results set for sp_columns is:

Column	Datatype	Description
`table_qualifier`	`varchar(32)`	The name of the database in which the table specified for the `table_name` parameter is stored.
`table_owner`	`varchar(32)`	The table owner. If no value was specified for the `table_owner` parameter, this value is the current owner or the Database Owner.
`table_name`	`varchar(32)`	NOT NULL.
`column_name`	`varchar(32)`	NOT NULL.
`data_type`	`smallint`	Integer code for ODBC datatype. If this is a datatype that cannot be mapped into an ODBC type, it is NULL.
`type_name`	`varchar(30)`	String representing a datatype. The underlying DBMS presents this datatype name.
`precision`	`int`	Number of significant digits.
`length`	`int`	Length in bytes of a datatype.
`scale`	`smallint`	Number of digits to the right of the decimal point.
`radix`	`smallint`	Base for numeric datatypes.
`nullable`	`smallint`	The value 1 means NULL is possible; 0 means NOT NULL.
`remarks`	`varchar(254)`
`ss_data_type`	`smallint`	An SAP ASE datatype.
`colid`	`tinyint`	A column appended to the results set.
`column_def`	`varchar(255)`	NULL.
`sql_data_type`	`smallint`	An SAP ASE datatype.
`sql_datetime_sub`	`smallint`	NULL.
`char_octet_length`	`int`	The value of `char_octet_length` is the same as the value for the `precision` column if the datatype for `char_octet_length` is: `binary` `char` `image` `nchar` `nvarchar` `sysname` `text` `timestamp` `varbinary` `varchar` Otherwise, the value of `char_octet_length` is 0.
`ordinal_position`	`int`	The ordinal position of the column in the table. The first column in the table is 1.
`is_nullable`	`varchar(3)`	Describes whether the column or parameter allows NULL as a value. From syscolumns.

sp_columns reports the type_name as float, and data_type as 6 for columns defined as double precision. The SAP ASE double precision datatype is a float implementation supports the range of values as specified in the ODBC specifications.

Permissions

Any user can execute sp_columns.