sp_helpindex

Description

Reports information about the indexes created on a table.

Syntax

sp_helpindex objname

Parameters

objname

is the name of a table in the current database.

Examples

Example 1

Displays the types of indexes on the sysobjects table:

sp_helpindex sysobjects

index_name           index_description 
      index_keys
      index_max_rows_per_page index_fillfactor index_reservepagegap 
 -------------------- -------------------------------------------------- 
        ----------------------- ---------------- -------------------- 
 sysobjects           clustered, unique located on system 
     id 
                              0                0                    0 
 ncsysobjects         nonclustered, unique located on system
     name,uid 
                              0                0                    0 

Example 2

The index on publ_ix was created with pub_id in ascending order and pubdate in descending order:

sp_helpindex titles

index_name       index_description
   index_keys
   index_max_rows_per_page index_fillfactor index_reservepagegap
---------------- ------------------------------------------------
title_id_ix      nonclustered, unique located on default
    title_id
                         0                0                    0
publ_ix          nonclustered located on default
    pub_id, pubdate DESC
                         0                0                    8
title_ix         clustered, allow duplicate rows located on default
    title
                         0               90                    0

Usage

Permissions

Any user can execute sp_helpindex.

See also

Commands create index, drop index, update statistics

System procedures sp_help, sp_helpkey