if exists (select * from [tempdb].[dbo].sysobjects where id = object_id(N‘[tempdb].[dbo].[##NonClusteredIndexesToDelete]’))
drop table ##NonClusteredIndexesToDelete
create Table ##NonClusteredIndexesToDelete
([Name] sysname, [TableName] sysname)
insert ##NonClusteredIndexesToDelete
select
sysindexes.[name],
sysobjects.[name] as TableName
from
sysindexes (nolock)
inner join
sysobjects on sysindexes.id = sysobjects.id
where (indid > 1) — delete only non clustered indexes
and (sysindexes.[name] not like ‘_WA%’) — don’t delete statistics
and (sysobjects.[type] != ‘S’) — don’t delete system indexes
and (sysobjects.[xtype] != ‘IT’) — don’t delete system indexes
and (not(sysindexes.[status] = 4098 and sysindexes.indid = 2)) — don’t delete unique indexes
order by (sysindexes.[name])
declare @name sysname
declare @TableName sysname
declare @sql varchar(4096)
declare vCursor cursor for
select [name], [TableName] from ##NonClusteredIndexesToDelete order by [name]
open vCursor
fetch NEXT from vCursor into @name, @TableName
while @@FETCH_STATUS = 0
begin
set @sql = ‘drop index ‘ + @TableName + ‘.’ + @name
exec (@sql)
fetch NEXT from vCursor into @name, @TableName
End
close vCursor
deallocate vCursor
drop table ##NonClusteredIndexesToDelete