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. I found this necessary when I was writing a kind of O/R mapper software. Anyways 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.
Alter Procedure dbo.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
Grant Execute On dbo.DeleteAllProcedures To Public
Go
