Drop all Stored Procedures in database in SQL Server 2008

In this article I describe how to drop all stored procedures from a given database.
  • 2353

Stored Procedure

Stored procedure is a set of logical SQL statements to perform a specific task such as insert, select, update and delete operations on a table and so on which is stored in a SQL Server database. We don't need to compile again these stored procedure at using time. It makes stored procedure faster than execution of normal SQL statement. You can drop all stored procedures from a particular database in SQL Server 2008. This is very useful for dropping large number of stored procedures because it will be very difficult to drop procedures one by one. To drop all stored procedures from database see following example:

Drop all stored procedures from a database

declare @procName varchar(500)

declare cur cursor

    for select [name] from sys.objects where xtype = 'p'

open cur

 

fetch next from cur into @procName

      while @@fetch_status = 0

      begin

            if @procName <> 'DeleteAllProcedures'

                  exec ('drop procedure ' + @procName)

                  fetch next from cur into @procName

      end

 

close cur

deallocate cur

Output:

output-in-sql.jpg

Now select the Stored Procedure in the Database:

select [name] from sysobjects where type='v'

Output:

dropping-all-storedProcedures-from-database.jpg

© 2013 dotNetheaven. All rights reserved.