sp_columns

Description

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 Adaptive Server datatype.
colid	tinyint	A column appended to the results set.
column_def	varchar(255)	NULL.
sql_data_type	smallint	An Adaptive Server 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 Adaptive Server 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.