Chapter 11: 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. Views, aliases, and wildcard-character search patterns are not supported. Views and aliases are not supported.

table_owner: is the owner of the database object about which column privilege information is requested. Wildcard-character search patterns are not supported. If you do not specify this parameter, sp_statistics looks first for a table owned by the current user and then for a table owned by the database owner.

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.

Table 11-14: Result set for *sp_statistics*
Column	Datatype	Description
TABLE_QUALIFIER	varchar(128)	Always NULL.
TABLE_OWNER	varchar(128)	Table owner authorization ID.
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.

View this book as PDF