Returns information about the type of data that can be stored in one or more columns.
server_name... 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.
is ignored. Null should be specified.
is the name of the column for which you want information. Use wildcard characters to request information about more than one column.
The following example displays information about all columns in the publishers table that begin with “p”:
1> DCSERVER...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 remote_data_type -------------------------------- ------------------------------ ------------------------------ ------------------------------ ----------- ---------------------------------------------------------------------------------------------------------- ----------- ----------- ----------- ----------- ----------- -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------------ ----------- ---------------- NULL SYSTEM PUBLISHERS PUB_ID 1 CHAR NULL 4 NULL 0 0 NULL 47 1 96 NULL SYSTEM PUBLISHERS PUB_NAME 12 VARCHAR2 NULL 40 NULL 0 1 NULL 39 2 1 (2 rows affected) (return status = 0)
Table 4-1 shows the results set:
Column |
Datatype |
Description |
|---|---|---|
table_qualifier |
varchar(32) |
Always NULL. |
table_owner |
varchar(32) |
The table owner. If no value was specified for the table_owner parameter, this value is the current owner. |
table_name |
varchar(32) |
The table name. |
column_name |
varchar(32) |
The column name. |
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 |
The column ID. |
remote_data_type |
The Oracle datatype |
Any user can execute sp_columns.
