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.

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.

Table 6-17 shows the result set for sp_statistics.

**Table 6-17: 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) – the index prohibits duplicate values. 1 (TRUE) – 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 – statistics for a table. 1 SQL_INDEX_CLUSTERED – a clustered index. 2 SQL_INDEX_HASHED – a hashed index. 3 SQL_INDEX_OTHER – 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 – ascending. D – descending. NULL – 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.