Brian Cryer

 

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')

In SQL Server 2000 and earlier this was:

select name from sysindexes where id=object_id('table_name')

for example:

exec sp_helpindex Employees

or

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

or in SQL Server 7 or 2000:

select name from sysindexes where 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.



About the author: is a dedicated software developer and webmaster. For his day job he develops websites and desktop applications as well as providing IT services. He moonlights as a technical author and consultant.