sp_helpindex (Transact-SQL)

Applies to: SQL Server Azure SQL Database Azure SQL Managed Instance

Reports information about the indexes on a table or view.

Transact-SQL syntax conventions

Syntax

  
sp_helpindex [ @objname = ] 'name'  

Arguments

[ @objname = ] 'name' Is the qualified or nonqualified name of a user-defined table or view. Quotation marks are required only if a qualified table or view name is specified. If a fully qualified name, including a database name, is provided, the database name must be the name of the current database. name is nvarchar(776), with no default.

Return Code Values

0 (success) or 1 (failure)

Result Sets

Column name Data type Description
index_name sysname Index name.
index_description varchar(210) Index description including the filegroup it is located on.
index_keys nvarchar(2078) Table or view columns upon which the index is built.

A descending indexed column will be listed in the result set with a minus sign (-) following its name; an ascending indexed column, the default, will be listed by its name alone.

Remarks

If indexes have been set by using the NORECOMPUTE option of UPDATE STATISTICS, that information is included in the index_description column.

sp_helpindex exposes only orderable index columns; therefore, it does not expose information about XML indexes or spatial indexes.

Permissions

Requires membership in the public role.

Examples

The following example reports on the types of indexes on the Customer table.

USE AdventureWorks2022;  
GO  
EXEC sp_helpindex N'Sales.Customer';  
GO  

See Also

Database Engine Stored Procedures (Transact-SQL)
sys.indexes (Transact-SQL)
sys.index_columns (Transact-SQL)
System Stored Procedures (Transact-SQL)
UPDATE STATISTICS (Transact-SQL)