-- Scripted by Sachin Wable
-- Use this script to Drop All user created indexes giving table name as Parameter

CREATE proc sp_Dropindex
	@objname nvarchar(776)
as
declare @NAME varchar(50),
             @objid int,
             @dbname sysname
if @dbname is not null and @dbname <> db_name()
begin
	raiserror(15250,-1,-1)
	return (1)
end
select @objid = object_id(@objname)
if @objid is NULL
begin
	raiserror(15001,-1,-1,@objname)
	return (1)
end

declare db cursor for
select name from sysindexes where
id=(select id from sysobjects where type ='u' and name=@objname)
and indid > 0 and indid < 255 and (status & 64)=0

open db
fetch next from db INTO @NAME
if @@fetch_status = -1
Begin
             raiserror(15472,-1,-1) --'Object does not have any indexes.'
             close db
            deallocate db
	return (1)
End

while @@fetch_status=0
BEGIN
   if @@fetch_status=0
        begin
   exec('Drop index '+@objname+'.'+@name)
END
    fetch next from db INTO @NAME
end
   close db
   deallocate db
GO