Drop Indexes

,

This script drop all indexes from table, including Primary Key contraint. First it del nonclustered indexes, then clustered and at last - drops PK contraint. This scirpt is useful, when you need drop all indexes, like when doing ETL process.

/*
Drop Indexes By Ramunas Balukonis, 2004.03.17
*/
CREATE proc dbo.pr_DropIndexes  (@objname nvarchar(776))-- the table to check for indexes)
as

declare@db_name sysname
declare @sqlExe nvarchar(4000)
set @db_name = db_name()

-- PRELIM
set nocount on

declare @indname sysname


select name as indname
, convert(bit, (status & 2048)) as Pk
, convert(bit, (status & 16)) as Clust 
into #spindtab
from sysindexes
where id = object_id (@objname) and indid > 0 and indid < 255 and (status & 64)=0 order by indid

declare crs_idx cursor fast_forward read_only for 
select indname from #spindtab where pk = 0 order by Clust

open crs_idx
fetch next from crs_idx into @indname
while @@fetch_status = 0
begin
set @sqlExe = 'drop index ' + @objname + '.' + @indname
/* drop indexes */--print (@sqlExe)
exec (@sqlExe)
fetch next from crs_idx into @indname
end
close crs_idx
deallocate crs_idx

/* drop constraint */select @indname = indname from #spindtab where pk = 1
if @@rowcount > 0
begin
set @sqlExe = 'alter table ' + @objname + ' drop constraint ' + @indname
--print (@sqlExe)
exec (@sqlExe)
end

return 0
GO

Rate

4 (1)

Share

Share

Rate

4 (1)