Reports information about the indexes created on a table. Reports information on computed column indexes and function-based indexes.
sp_helpindex objname
is the name of a table in the current database.
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
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
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
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)
sp_helpindex lists any indexes on a table, including indexes created by defining unique or primary key constraints defined by a create table or alter table statement.
sp_helpindex displays any attributes (for example, cache bindings) assigned to the indexes on a table.
sp_helpindex displays:
Partition information for each index.
Whether the index is local or global, clustered or nonclustered.
The max_rows_per_page setting of the indexes.
Information about clustered indexes on data-only locked tables.
The index ID (indid) of a clustered index in data-only locked tables is not equal to 1.
The column order of the keys, to indicate whether they are in ascending or descending order.
Space manage property values.
The key column name followed by the order. Only descending order is displayed. For example, if there is an index on column a ASC, b DESC, c ASC, “index_keys” shows “a, b DESC, c”.
Any user can execute sp_helpindex.
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 |
|
Commands create index, drop index, update statistics
System procedures sp_help, sp_helpkey, sp_helpartition
