Delete All Procedures from a database using a Stored procedure in SQL Server
Posted by Viral Sarvaiya on February 17, 2010
In this article I’ll try to explain how you can create a stored procedure that deletes all other stored procedures from a database in Microsoft SQL Server.Some time it is required that you delete all stored procedures from an SQL Server database.
this technique uses the build in sys.objects system table that contains all the objects of current database.
If you filter its selection with a where clause and select only those records that that have type = ‘P’ (this is for procedures) then you can get the names of all the procedures. You can store the list of the procedure names in a temporary table and loop delete the procedures or you can use a cursor. I’ve used a cursor and deleted the procedures one by one using the EXEC function.
Here is the code.
create Procedure DeleteAllProcedures As declare @procName varchar(500) declare cur cursor for select [name] from sys.objects where type = '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 Go
hope this will help full to you