Script to drop all indexes on a table

,

Given a table name as the parameter, all the indexes on the table can be dropped. This script can be useful if you want to drop indexes on a table inside another procedure 

if exists (select name from sysobjects where name = 'proc_drop_table_index' and type = 'P')
	drop proc proc_drop_table_index 
go


create proc proc_drop_table_index 
(
	@tab_name varchar(100)
)
as 

set nocount on 
declare @name varchar(50)
	,@errorsave int

if (rtrim(@tab_name) = '') 
	RAISERROR ('A non-zero length table name parameter is expected', 16, 1)	

BEGIN TRAN

if exists (select name from sysindexes
where id = object_id(@tab_name) and indid > 0 and indid < 255 and (status & 64)=0)
begin 
	declare ind_cursor cursor for 
	select name from sysindexes
	where id = object_id(@tab_name) and indid > 0 and indid < 255 and (status & 64)=0

	open ind_cursor
	fetch next from ind_cursor into @name
	while (@@fetch_status = 0)
	begin 
		exec ('drop index ' + @tab_name + '.' + @name)
		set @errorsave = @@error
		
		fetch next from ind_cursor into @name
	end
	close ind_cursor
	deallocate ind_cursor
end 

if (@errorsave = 0)
	COMMIT TRAN
else 
	ROLLBACK TRAN

GO

Rate

5 (1)

Share

Share

Rate

5 (1)