sp_helpindex

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

Syntax

sp_helpindex objname

Parameters

Examples

Usage

There are additional considerations when using sp_helpindex:
  • 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”.

See also create index, drop index, update statistics in Reference Manual: Commands.

Permissions

Any user can execute sp_helpindex. Permission checks do not differ based on the granular permissions settings.

Auditing

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

InformationValues
Event

38

Audit option

exec_procedure

Command or access audited

Execution of a procedure

Information in extrainfo
  • 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

Related reference
sp_help
sp_helpkey
sp_helpartition