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 table name or view. Use the wildcard character to request information about more than one table.
is the owner of the table or view. Use the wildcard character to request information about tables owned by more than one user.
is ignored. Leave blank or set to NULL.
is the name of the column for which you want information. Use the wildcard character to request information about more than one column.
This procedure returns the ASE 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.
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 datatype; 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 datatype 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.
ODBC datatype (TYPE_NAME) |
Target datatype length |
Datatype |
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.
Table 11-2 describes the datatype 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. |
