Returns information about the type of data that can be stored in one or more columns.
sp_columns table_name [, table_owner ] [, table_qualifier] [, column_name]
is the name of the table or view. Use wildcard characters to request information about more than one table.
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.
is the name of the database. This can be either the current database or NULL.
is the name of the column for which you want information. Use wildcard characters to request information about more than one column.
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
Displays information about all columns beginning with “st” in tables that begin with “s”:
sp_columns "s%", null, null, "st%"
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:
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.
Any user can execute sp_columns.
| Copyright © 2005. Sybase Inc. All rights reserved. |
|
|
