sp_statistics

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 – Shows the list of indexes for publishers:

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.