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. Use the wildcard character to request information about more than one table. Aliases are not supported.

table_owner: is the owner of the database object about which column information is requested. Use the wildcard character to request information about tables owned by more than one user. If you do not specify a table owner, sp_columns looks first for tables owned by the current user and then for tables owned by the database owner.

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. Leave empty or set to NULL to request information about all columns in the table or tables.

Usage

If column_name is provided, sp_columns returns information only for the column or columns that match.
This function corresponds to the ODBC function SQLColumns.
Information is based on the SYSCOLUMNS and SYSSYNONYMS system catalog tables.

Results

sp_columns returns one row containing a description of each column in a table. Results are ordered by the following columns:

TABLE_OWNER
TABLE_NAME

The lengths for varchar columns shown in the result set tables are maximums; the actual lengths depend on the target database.

Table 11-4 shows the result set.

**Table 11-4: Result set for sp_columns**
Column	Datatype	Description
TABLE_QUALIFIER	varchar(128)	Always NULL
TABLE_OWNER	varchar(128)	Table owner identifier
TABLE_NAME	varchar(128) NOT NULL	Table name
COLUMN_NAME	varchar(128) NOT NULL	Column name
DATA_TYPE	smallint NOT NULL	Integer code for the ODBC datatype
TYPE_NAME	varchar(128) NOT NULL	String representing the datatype name in the target database
PRECISION	int	Number of significant digits of the column on the target database
LENGTH	int	Length of the column in bytes
SCALE	smallint	Number of digits to the right of the decimal point
RADIX	smallint	Base for numeric types
NULLABLE	smallint NOT NULL	Indicates whether the column accepts NULL values: 0 SQL_NO_NULLS if the column does not accept NULL values 1 SQL_NULLABLE if the column accepts NULL values 2 SQL_NULLABLE_UNKNOWN if it is not known if the column accepts NULL values
REMARKS	varchar(254)	A description of the column
SS_DATA_TYPE	smallint	The ASE datatype name
COLID	smallint	The column ID number
REMOTE_DATA_TYPE	int	An integer representing the underlying target database datatype (composite value)

Table 11-5 describes the DB2 datatypes and matching ODBC integer identifiers that are returned in the TYPE_NAME and DATA_TYPE columns of the sp_columns, sp_datatype_info, sp_special_columns, and sp_sproc_columns result sets.

**Table 11-5: ODBC datatypes**
DB2 datatype (TYPE_NAME)	Target datatype maximum physical length	ODBC type	ODBC integer ID (DATA_ TYPE)	DB2 datatype description
CHARACTER() FOR BIT DATA	254	SQL_BINARY	-2	Fixed length character for bit data
VARCHAR() FOR BIT DATA	254	SQL_VARBINARY	-3	Variable length character for bit data
LONG VARCHAR FOR BIT DATA	32714	SQL_LONGVARBINARY	-4	Variable length character for bit data
CHARACTER()	254	SQL_CHAR	1	Fixed length character
VARCHAR()	254	SQL_VARCHAR	12	Variable length character
LONG VARCHAR()	32714	SQL_LONGVARCHAR	-1	Variable length character
CHARACTER() FOR MIXED DATA	254	SQL_BINARY	-2	Fixed length character (DBCS or SBCS)
VARCHAR() FOR MIXED DATA	254	SQL_VARBINARY	-3	Variable length character (DBCS or SBCS)
LONG VARCHAR() FOR MIXED DATA	32714	SQL_LONGVARBINARY	-4	Variable length character (DBCS or SBCS)
GRAPHIC()	127	SQL_BINARY	-2	Fixed length graphic (DBCS)
VARGRAPHIC()	127	SQL_VARBINARY	-3	Variable length graphic (DBCS)
LONG VARGRAPHIC	16357	SQL_LONGVARBINARY	-4	Variable length graphic (DBCS)
SMALLINT	2	SQL_SMALLINT	5	2-byte binary integer
INTEGER	4	SQL_INTEGER	4	4-byte binary integer
REAL	4	SQL_REAL	7	4-byte floating point
FLOAT()	4	SQL_REAL	7	4-byte floating point with a precision less than 22
FLOAT()	8	SQL_DOUBLE	8	8-byte floating point with a precision equal to or greater than 22
DOUBLE PRECISION	8	SQL_DOUBLE	8	8-byte floating point
DECIMAL()	31	SQL_DECIMAL	3	Packed decimal number
NUMERIC	31	SQL_NUMERIC	2	Zoned decimal number
DATE	10	SQL_DATE	9	Date
TIME	8	SQL_TIME	10	Time
TIMESTAMP	26	SQL_DATETIME	11	Timestamp

The REMOTE_DATATYPE column contains a 32-bit composite datatype value that represents the target database datatype.

Table 11-6 describes the datatype value.

**Table 11-6: *REMOTE_DATATYPE* value**
Bit(s)	Description
Bits 0-7	ODBC (target) datatype (can be extended for types not defined in ODBC)
Bit 8	Returns 1 if nullable, 0 if not nullable
Bit 9	Returns 1 if case sensitive, 0 if not case sensitive
Bits 10, 11	Always returns 10 (binary), which means updatability unknown
Bits 12, 13	Reserved, always returns 00 (binary)
Bits 14, 15	Returns the following: 01 (binary), which means NEWODBCDATATYPE (used for all except REAL) 10 (binary), which means NEWUSERTYPE (used for REAL)
For numeric types: Bits 16–23 Bits 24–31	Precision Scale
For non-numeric types: Bits 16–31	Length