How to list indexes for all tables 1

[ColinGregory]

Does anyone know how to create a new table or view showing what indexes exist for all the tables in a database??

The information I need to know is table name, index name and index type. I've been messing about with combining sp_msforeachtable and sp_helpindex but with no success.

Thanks in advance

 

[RickCole]

All the indexs are held in a system table within the database called sysindexes. Used in conjunction with the system table sysobjects the following query should get you started on what you want.

select sysobjects.name as Table_name, sysindexes.name as Index_name
from sysobjects , sysindexes
where sysobjects.type = 'U' and sysobjects.id = sysindexes.id


The type 'U' in sysobjects means you will only pick up the User tables you have created within the database.

Rick.