Cry How to...

List all indexes on a table

To list all the indexes on a table use:

exec sp_helpindex table_name

where table_name is the name of the table for which the list of indexes are required.

This lists the index name, the type (e.g. clustered, non clustered, unique etc) and which columns the index is created on. Further details can be found by consulting the SQL server documentation.

Alternately, just to list the names of the indexes (with no further information about the index) you could use:

select name from sys.indexes where object_id=object_id('table_name')

for example:

exec sp_helpindex Employees

or

select name from sys.indexes where object_id=object_id('Employees')

both produce the same list of index names, but the first also provides information about each of those indexes.

These notes have been tested against SQL Server 7, 2000, 2005 and 2008.