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_statistics

Usage

If index_name is specified, sp_statistics returns only information about that index.
This function corresponds to the ODBC function SQLStatistics.

Results

sp_statistics returns information about the named table. Results are ordered by the following columns:

NON_UNIQUE
TYPE
INDEX_QUALIFIER
INDEX_NAME
SEQ_IN_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_statistics*
Column	Datatype	Description
TABLE_QUALIFIER	varchar(128)	Always NULL
TABLE_OWNER	varchar(128)	Name of the collection, library, or NULL
TABLE_NAME	varchar(128) NOT NULL	Name of the table or view
NON_UNIQUE	smallint	Indicates whether the index permits duplicate values: 0 (FALSE) means the index prohibits duplicate values 1 (TRUE) means the index allows duplicate values NULL is returned if TYPE is SQL_TABLE_STAT
INDEX_QUALIFIER	varchar(128)	Always NULL
INDEX_NAME	varchar(128)	Index name; NULL is returned if TYPE is SQL_TABLE_STAT.
TYPE	smallint NOT NULL	Type of information returned: 0 SQL_TABLE_STAT means statistics for a table 1 SQL_INDEX_CLUSTERED means a clustered index 2 SQL_INDEX_HASHED means a hashed index 3 SQL_INDEX_OTHER means another type of index
SEQ_IN_INDEX	smallint	Sequence of the column in the index (the first column is 1); NULL is returned if TYPE is SQL_TABLE_STAT.
COLUMN_NAME	varchar(128)	Column name; NULL is returned if TYPE is SQL_TABLE_STAT.
COLLATION	char(1)	Sort sequence for the column: A means ascending D means descending NULL is returned if TYPE is SQL_TABLE_STAT
CARDINALITY	int	Cardinality of the table or index: Number of rows in the table if TYPE is SQL_TABLE_STAT Number of unique values in the index if TYPE is not SQL_TABLE_STAT NULL if the value is not available from the target database
PAGES	int	Number of pages used to store the index or table: Number of pages used to store the table if TYPE is SQL_TABLE_STAT Number of pages used to store the index if TYPE is not SQL_TABLE_STAT NULL if this information is not available from the target database
FILTER_CONDITION	varchar(128)	If the index is a filtered index, this is the filter condition; if the filter condition cannot be determined, this is an empty string. NULL is returned if the index is not a filtered index or TYPE is SQL_TABLE_STAT.


	Expand Search