Here is a simple stored procedure I created -- called Sp_Dropindexes -- that allows you to drop all user-created indexes of a given table. Sp_ Dropindexes takes a single parameter (@objname) and drops all of its user-created indexes.
Note that I created this procedure in SQL Server 2000 using temporary tables. Here goes:
-- 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
Art D. notes: "This SP will drop PK's too, as well as indices on system tables."
For More Information
- What do you think about this tip? E-mail us at email@example.com with your feedback.
- The Best SQL Server Web Links: tips, tutorials, scripts, and more.
- Have an SQL Server tip to offer your fellow DBA's and developers? The best tips submitted will receive a cool prize--submit your tip today!
- Ask your technical SQL Server questions--or help out your peers by answering them--in our live discussion forums.
- Check out our Ask the Experts feature: Our SQL, Database Design, Oracle, SQL Server, DB2, metadata, and data warehousing gurus are waiting to answer your toughest questions.