Relational Database Systems

Generate List of Indexes for all Tables – MS SQL Server

Following Script returns list of indexes with detail for all tables of selected database:


if object_id('tempdb..#tblIndex')>0
drop table #tblIndex

create table #tblIndex
(
Table_name varchar(1000),
index_name varchar(1000),
index_desc varchar(2000),
index_keys varchar(1000)
)

declare @tblName varchar(250)

declare cur cursor for select name from sysobjects where xtype = 'U'
open cur

fetch cur into @tblName

while @@fetch_status = 0
begin

insert into #tblIndex (index_name, index_desc, index_keys) exec ('sp_helpindex [‘ + @tblName + ‘]')
update #tblIndex
set Table_Name = @tblName
where Table_Name is NULL

fetch cur into @tblName

end
close cur
deallocate cur

select Table_Name
,index_name
,index_desc
,RIGHT(index_desc,len(index_desc) – (charindex(' on ',index_desc,1) + 3)) FileGroup
,index_keys from #tblIndex
order by Table_Name

Leave a Reply

Your email address will not be published. Required fields are marked *

To Top