Retrieving all indexes of the Database.

,

The Script outputs all the indexes in the Database.

I have used the sp_helpIndex system stored procedure here to retrieve all the indexes available for the Table . This is extremely useful in identifying all the indexes in a database in a single query

*	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

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

Rate

5 (2)

Share

Share

Rate

5 (2)