Chapter 11: Accessing Catalog Information with CSPs

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 table name or view. Use the wildcard character to request information about more than one table.

table_owner: is the owner of the table or view. Use the wildcard character to request information about tables owned by more than one user.

table_qualifier: is ignored. Leave blank or set to NULL.

column_name: is the name of the column for which you want information. Use the wildcard character to request information about more than one column.

Usage

This procedure returns the Adaptive Server datatype that most clearly matches the native datatype of the target, regardless of the current datatype properties.
This procedure corresponds to the ODBC function SQLColumns.

Results

This procedure returns one row containing a description of each column in a table.

There are three columns in the result set that describe each columns data type; TYPE_NAME, DATA_TYPE, and REMOTE_DATA_TYPE.
Table 11-1 describes the values returned in the TYPE_NAME and DATA_TYPE columns of the result set. TYPE_NAME contains the ODBC data type name and DATA_TYPE contains the ODBC integer identifier.
The REMOTE_DATA_TYPE column contains a 32-bit or 4-byte composite user datatype UDT specifically identifying the remote datatype. See Table 11-2.

Table 11-1 also describes the identifiers returned in the TYPE_NAME and DATA_TYPE columns in the result set for sp_special_columns.

**Table 11-1: ODBC Datatypes**
ODBC datatype (TYPE_NAME)	Target datatype length	DATA-TYPE	ODBC type	Sybase type
BINARY	254	(-2)	SQL_BINARY	CS_BINARY
VARBINARY	254	(-3)	SQL_VARBINARY	CS_VARBINARY
LONGVARBINARY	2^31	(-4)	SQL_LONGVARBINARY	CS_LONGBINARY
CHAR()	254	(1)	SQL_CHAR	CS_CHAR
VARCHAR	254	(12)	SQL_VARCHAR	CS_VARCHAR
LONGVARCHAR	2^31	(-1)	SQL_LONGVARCHAR	CS_LONGCHAR
SMALLINT	2	(5)	SQL_SMALLINT	CS_SMALLINT
INTEGER	4	(4)	SQL_INTEGER	CS_INT
DOUBLE	8	(8)	SQL_DOUBLE	CS_FLOAT
FLOAT()	8	(6)	SQL_FLOAT	CS_FLOAT
REAL	4	(7)	SQL_REL	CS_REAL
DECIMAL()	17	(3)	SQL_DECIMAL	CS_DECIMAL
NUMERIC	17	(2)	SQL_NUMERIC	CS_NUMERIC
DATE	4	(9)	SQL_DATE	CS_DATE
TIME		(10)	SQL_TIME	CS_TIME
TIMESTAMP	10	(11)	SQL_TIMESTAMP	CS_DATETIME
TINYINT	1	(-6)	SQL_TINYINT	CS_TINYINT
BIGINT	19	(-5)	SQL_BIGINT	CS_FLOAT
BIT	1	(-7)	SQL_BIT	CS_BIT
GUID	36	-11	SQL_GUID	CS_CHAR
WCHAR	254	-8	SQL _WCHAR	CS_UNICHAR
WVARCHAR	254	-9	SQL _WVARCHAR	CS_UNICHAR
WLONGVARCHAR	230	-10	SQL _WLONGVARCHAR	CS_UNITEXT

This procedure allows transmission of column datatypes using a target-specific-type ID. The REMOTE_DATA_TYPE column contains a 32-bit composite datatype defined by the access service.

The following table describes the datatype value.

**Table 11-2: **REMOTE_DATA_TYPE** return value**
Bits	Value returned
Bits 0-7	ODBC datatype (can be extended for types not defined in ODBC).
Bit 8	1 if nullable, 0 if not nullable.
Bit 9	1 if case sensitive, 0 if not case sensitive.
Bits 10, 11	10 (binary); ability to be updated unknown.
Bits 12, 13	Reserved; always returns 00 (binary). The access service bulk copy feature uses this.
Bits 14, 15	01 (binary); NEWODBCDATATYPE (used for all except REAL) 10 (binary); NEWUSERTYPE (used for REAL).
Numeric types: Bits 17–23 Bits 24–31	Precision. Scale.
Non-numeric types: Bits 16–31	Length.

View this book as PDF