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

Description

Retrieves the following information about columns within a specified table or view:

The optimal set of columns that uniquely identify a row in the table or view
A list of the columns that are automatically updated when any value in the row is updated

Syntax

sp_special_columns table_name [, table_owner]
 [, table_qualifier] [, col_type]

Parameters

table_name

is the name of the table. Wildcard-character search patterns are not supported. Views are not supported.

table_owner

is the name of the collection if the CSPCatalogQualifier property value is QSYS2. Otherwise, this parameter can be ignored, set to NULL, or set to the value of the CSPCatalogQualifier property.

table_qualifier

is ignored. Leave blank or set to NULL.

col_type

is a value that requests information about columns of a specific type as follows:

"R" returns information about columns with values that uniquely identify any row in the table.
"V" returns information about columns with values that are automatically generated by a target each time a row is inserted or updated.

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