sp_column_privileges  sp_databases

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


Results

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

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

The following table 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 Adaptive Server datatype name

COLID

smallint

The column ID number

REMOTE_DATA_TYPE

int

An integer representing the underlying target database datatype (composite value)

ODBC Datatypes

The following table 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

REMOTE_DATATYPE

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

The following table 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) meaning updatability unknown

Bits 12, 13

Reserved, always returns 00 (binary)

Bits 14, 15

Returns the following:

  • 01 (binary) meaning NEWODBCDATATYPE (used for all except REAL)

  • 10 (binary) meaning NEWUSERTYPE (used for REAL)

For numeric types: Bits 16–23 Bits 24–31

Precision Scale

For non-numeric types: Bits 16–31

Length



Copyright © 2005. Sybase Inc. All rights reserved. sp_databases

View this book as PDF