Retrieving all indexes of the Database.

*Author:      Suresh Ramakrishnan 
*Purpose:      This displays all the indexes available in the Database       
*Effects: None.


Begin

Declare @objectid varchar (75)

Set nocount on

Declare getindexkeys cursor local static for

Select name from sysobjects where xtype='U' and name in (
Select object_name(id) from sysindexes  
Where object_name (id) in (Select name from sysobjects where 
type in ('U','V') ) and indid=1 )

Open getindexkeys 

Create table #tblindextemp 
(
TableName Varchar (75) null,
index_name varchar (200),
index_description varchar(500),
index_cols varchar(200)
)


Fetch next from getindexkeys into @objectid

While @@fetch_status=0

Begin
 
Insert into #tblindextemp (index_name,index_description,index_cols)

Execute sp_helpindex @objectid 

Update #tblindextemp set TableName =@objectid where TableName is null

Fetch next from getindexkeys into @objectid

End 

Select * from #tblindextemp

Close getindexkeys

Deallocate getindexkeys

Drop table #tblindextemp

End

*****************************************************************