Drop Indexes in a Database

set nocount on

go

DECLARE @sql VARCHAR(2000),@DB VARCHAR(100),@count int,@TableName varchar(200),

@Index_Name varchar(200),@ConstraintName varchar(200)

SET @DB = db_name()

if exists(select * from msdb..sysobjects where name =

'DropIndexes_AllTables')

drop table msdb.dbo.DropIndexes_AllTables

CREATE TABLE msdb.dbo.DropIndexes_AllTables(tablename

varchar(200),ConstraintName varchar(200),Index_Name varchar(200),type

varchar(10))

SELECT @SQL = 'USE '+@db+char(13)+'SELECT object_name(parent_object_id)

TableName,sobj.Name ConstraintName,sidx.name Index_Name,type FROM sys.objects

sobj

join '+@db+'.dbo.sysindexes sidx on sobj.parent_object_id = sidx.id

where indid > 0 and type in

(''u'',''PK'',''UQ'') and

sidx.name not like ''_WA_sys_%'''

print @sql

insert into msdb.dbo.DropIndexes_AllTables

exec(@sql)

create table #temp_baby(ConstraintName varchar(200),TableName varchar(150))

select ConstraintName,TableName, Index_Name,Type from

msdb.dbo.DropIndexes_AllTables

insert into #temp_baby(ConstraintName,TableName)

select distinct ConstraintName,TableName from msdb.dbo.DropIndexes_AllTables

select @count = count(*) from #temp_baby

while(@count > 0)

begin

select top 1 @ConstraintName = ConstraintName,@tablename = tablename from

#temp_baby

select @sql = 'USE '+@db+char(13)+'alter table '+@tablename+' drop

constraint '+@ConstraintName+''

print @sql

delete from #temp_baby where ConstraintName = @ConstraintName and tablename =

@tablename

select @count = count(*) from #temp_baby

end

insert into #temp_baby(ConstraintName,TableName)

select distinct ConstraintName,TableName from msdb.dbo.DropIndexes_AllTables

print '------'

delete from msdb.dbo.DropIndexes_AllTables where Index_Name in (select

ConstraintName from #temp_baby)

select @count = count(*) from msdb.dbo.DropIndexes_AllTables

while(@count > 0)

Begin

select top 1 @tablename = TableName,@Index_name = Index_Name from

msdb.dbo.DropIndexes_AllTables order by tablename

select @sql = 'USE '+@DB+';drop index

'+@tablename+'.'+@Index_name+''

print @sql

--print @tablename, @Index_name

delete from msdb.dbo.DropIndexes_AllTables where TableName = @tablename and

Index_Name = @Index_name

select @count = count(*) from msdb.dbo.DropIndexes_AllTables

end

drop table #temp_baby