To see the indexes that exist on a table, use the sp_helpindex system procedure. A maximum of eight indexes will be displayed. The sysindexes system table contains information about all indexes.
Here's a sample report about the authors table:
sp_helpindex authors
index_name |
index_description |
index_keys |
------------- |
------------------------------- |
----------------- |
UPKCL_auidind |
clustered, unique, primary key located on default |
au_id |
aunmind |
nonclustered located on default |
au_lname, au_fname |
(1 row(s) affected)
When used with the table name, the sp_help system procedure also reports indexes on a specific table.