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.
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:
Now select the Stored Procedure in the Database:
select [name] from sysobjects where type='v'
Output:
