Reports information about the indexes created on a table.
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_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
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
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:
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.
Commands create index, drop index, update statistics
System procedures sp_help, sp_helpkey
