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

Leave a Reply

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.