sp_statistics

Description

Returns a list of indexes on a single table.

Syntax

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

Parameters

table_name: is the name of the table. The use of wildcard character pattern matching is not supported.

table_owner: is the owner of the table. The use of wildcard character pattern matching is not supported. If table_owner is not specified, sp_statistics looks for a table owned by the current user and then for a table owned by the Database Owner.

table_qualifier: is the name of the database. This can be either the current database or NULL.

index_name: is the index name. The use of wildcard character pattern matching is not supported.

is_unique: is Y to return only unique indexes; otherwise, is N to return both unique and nonunique indexes.

Examples

Example 1

sp_statistics publishers

table_qualifier                  table_owner
    table_name                       non_unique
    index_qualifier                  index_name
    type   seq_in_index column_name                     collation
    cardinality pages       
-------------------------------- --------------------------------
   -------------------------------- ---------- 
   -------------------------------- ---------------------------
   ------ ------------ -------------------------------- -------- 
   ----------- ----------- 
pubs2                            dbo
    publishers                       NULL
    NULL                             NULL
        0         NULL NULL                             NULL
             3           1 
pubs2                            dbo
    publishers                                0
    publishers                       pubind
         1            1 pub_id                           A
             3           1

Usage

The results set for sp_statistics is:

Column	Datatype	Description
table_qualifier	varchar(32)	The database name. This field can be NULL.
table_owner	varchar(32)
table_name	varchar(32)	NOT NULL.
non_unique	smallint	NOT NULL. The value 0 means unique, and 1 means not unique.
index_qualifier	varchar(32)
index_name	varchar(32)
type	smallint	NOT NULL. The value 0 means clustered, 2 means hashed, and 3 means other.
seq_in_index	smallint	NOT NULL.
column_name	varchar(32)	NOT NULL.
collation	char(1)	The value A means ascending; D means descending; and NULL means not applicable.
cardinality	int	Number of rows in the table or unique values in the index.
pages	int	Number of pages to store the index or table.

The indexes in the results set appear in ascending order, ordered by the non-unique, type, index_name, and seq_in_index columns.
The index type hashed accepts exact match or range searches, but searches involving pattern matching do not use the index.

Permissions

Any user can execute sp_statistics.