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

Description

Returns statistics information for a single table and the indexes associated with that table.

Syntax

sp_statistics table_name [, table_owner]
 [, table_qualifier] [, index_name] [, is_unique]

Parameters

table_name

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

index_name

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

is_unique

is one of the following values: "Y" if unique indexes are to be returned "N" if unique indexes are not to be returned

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