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')
exec sp_helpindex Employees
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: Brian Cryer 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.