Procedure to drop all user-created indexes of given table

A simple stored procedure that allows you to drop all user created index of a given table.

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 

Reader feedback

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 tdichiara@techtarget.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.

This was first published in November 2001

Dig Deeper

PRO+

Content

Find more PRO+ content and other member only offers, here.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchBusinessAnalytics

SearchDataCenter

SearchDataManagement

SearchAWS

SearchOracle

SearchContentManagement

SearchWindowsServer

Close