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.
  • 4434

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


            if @procName <> 'DeleteAllProcedures'

                  exec ('drop procedure ' + @procName)

                  fetch next from cur into @procName



close cur

deallocate cur



Now select the Stored Procedure in the Database:

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



© 2020 DotNetHeaven. All rights reserved.