Access Service User's Guide DirectConnect for AS/400

Home

Access Service User's Guide DirectConnect for AS/400

Chapter 12 Accessing catalog information with CSPs

sp_special_columns

Usage

This function corresponds to the ODBC function SQLSpecialColumns.
Information is based on the SYSINDEXES, SYSKEYS, and SYSCOLUMNS system catalog tables.

Results

sp_special_columns returns information about the columns that uniquely identify a row in a table.

The result set consists of a row for each column of an index that uniquely identifies each row of the table. If there are multiple unique indexes on a table, the one that is described by the result set is the first that exists in the following list:

A primary key with clustered index
A primary key without clustered index
A unique, clustered index
A unique, non-clustered index

The result set is ordered by the column name in the index. 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.

Result set for *sp_special_columns*
Column	Datatype	Description
SCOPE	smallint NOT NULL	Actual scope of the row ID: 0 SQL_SCOPE_CURROW 1 SQL_SCOPE_TRANSACTION
COLUMN_NAME	varchar(128) NOT NULL	Column name.
DATA_TYPE	smallint NOT NULL	ODBC datatype to which all columns of this type are mapped.
TYPE_NAME	varchar(128) NOT NULL	Name of the target database datatype that corresponds to the ODBC datatype in the DATA_TYPE column.
PRECISION	int	Maximum precision for the datatype in the target database; NULL if precision is not applicable.
LENGTH	int	Length of the column in bytes
SCALE	smallint	Number of digits to the right of the decimal point; NULL if scale is not applicable.
PSEUDO_COLUMN	smallint	Indicates whether the column is a pseudo-column. The access service always returns: 0 SQL_PC_UNKNOWN

For a description of AS/400 stored procedures, see .


	Expand Search