Reports information about the indexes created on a table. Reports information on computed column indexes and function-based indexes.
sp_helpindex objname
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
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
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
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.
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”.
See also create index, drop index, update statistics in Reference Manual: Commands.
Any user can execute sp_helpindex. Permission checks do not differ based on the granular permissions settings.
Values in event and extrainfo columns from the sysaudits table are:
Information | Values |
---|---|
Event | 38 |
Audit option | exec_procedure |
Command or access audited | Execution of a procedure |
Information in extrainfo |
|