if exists (select * from sysobjects where id = object_id('dbo.sp_drop_index_all') and sysstat & 0xf = 4)
	drop procedure dbo.sp_drop_index_all
GO

/* Procedure 	:	sp_drop_index_all							*/
/* Purpose		:	To drop all indexes from the input table	*/
/* Author		:	Narasimhan  Jayachandran					*/

create proc sp_drop_index_all 
@table varchar(30) = null
as 
begin

declare @index varchar(30)

set nocount on

if @table is null
begin
	select "Usage :: sp_drop_index_all <table name>"
	return 1
end

if not exists(select id from sysobjects where name = @table and type = 'U')
begin
	select @table+ ' table not available in the Database '+db_name()
	return 1
end

declare drop_index_cur cursor 
for select name from sysindexes where id=object_id(@table)  and indid > 0 and indid < 255
open drop_index_cur

fetch next from drop_index_cur into @index

while @@fetch_status = 0
begin
	exec("drop index "+@table+'.'+@index)
	if @@error <> 0
        begin 
		select 'Error occurred while dropping index '+@index+' from table '+@table
		return 1

	end
	select 'index '+@index+' dropped from table '+@table
	fetch next from drop_index_cur into @index
end

close drop_index_cur
deallocate drop_index_cur

return 0
end
GO