sp_helpindex

Description

Reports information about the indexes created on a table. Reports information on computed column indexes and function-based indexes.

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_keys index_description index_max_rows_per_page 
       index_fillfactor index_reservepagegap index_created
       index_local

sysobjects    id        clustered, unique                    0
                   0                  0        Apr 12 2005  2:38PM
        Global Index
ncsysobjects  name, uid nonclustered, unique
                   0                  0        Apr 12 2005  2:38PM
        Global Index

(2 rows affected)
index_pt_name          index_ptn_seg
---------------------- --------------
sysobjects_1            system
ncsysobjects_1           system

Example 2

Displays information about the index on the titles table in the pubs2 database. The titles table is partitioned, but the index titleind is not. titleind is a nonclustered (single-partitioned), global index.

sp_helpindex titles
index_name index_keys index_description index_max_rows_per_page 
       index_fillfactor index_reservepagegap index_created
       index_local

titleind    title    nonclustered                            0
          Global Index

(1 row affected)
index_pt_name          index_ptn_seg
---------------------- --------------
titleind_1232004389     default

Example 3

Displays index information about the mysalesdetail table. mysalesdetail is partitioned by hash on the ord_num column. A clustered, local index, with three partitions, has also been created on ord_num.

sp_helpindex mysalesdetail
index_name index_keys index_description index_max_rows_per_page
       index_fillfactor index_reservepagegap index_created       index_local
---------- ---------- ----------------- -----------------------
       ---------------- -------------------- -------------       -----------
clust_idx  ord_num    clustered                               0
                     0                     0 Apr 12 2005 2:38PM  Local Index
(1 row affected)
index_pt_name          index_ptn_seg
---------------------- --------------
clust_idx_1344004788   default
clust_idx_1360004845   default
clust_idx_1376004902   default

Example 4

Displays a function-based index:

create index sum_sales on mytitles (price * total_sales)
sp_helpindex mytitles
Object has the following indexes

index_name index_keys index_description index_max_rows_per_page
       index_fillfactor index_reservepagegap index_created       index_local
---------- ---------- ----------------- -----------------------
       ---------------- -------------------- -------------       -----------
sum_sales  sybfi2_1   nonclustered                            0
       0                                   0 Oct 12 2005 3:34PM  Global Index

(1 row affected)
index_ptn_name       index_ptn_seg
-------------------- -------------
sum_sales_1724867646 default 

(1 row affected)

Object has the following functional index keys

Internal_Index_Key_Name
-----------------------
sybfi2_1 

(1 row affected)

Expression
-------------------
price * total_sales

(return status = 0)

Usage

Permissions

Any user can execute sp_helpindex.

Auditing

Values in event and extrainfo columns from the sysaudits table are:

Event

Audit option

Command or access audited

Information in extrainfo

38

exec_procedure

Execution of a procedure

  • Roles – Current active roles

  • Keywords or options – NULL

  • Previous value – NULL

  • Current value – NULL

  • Other information – All input parameters

  • Proxy information – Original login name, if set proxy in effect

See also

Commands create index, drop index, update statistics

System procedures sp_help, sp_helpkey, sp_helpartition