sp_columns

Description

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

Syntax

server_name... 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.

table_qualifier

is ignored. Null should be specified.

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

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)

Usage

• Table 4-1 shows the results set:

  Table 4-1: Results set for sp_columns

  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

Permissions

Any user can execute sp_columns.

Copyright © 2005. Sybase Inc. All rights reserved.

View this book as PDF