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

Permissions

Any user can execute sp_columns.