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)
end
close cur
deallocate cur
Go
Grant Execute On dbo.DeleteAllProcedures To Public
Be very careful when calling this stored procedure. It’s really useful for special purposes and not for every day use.
Posted on 5/18/2007 1:23:49 PM by Manish Kaushik
Posted on 6/25/2007 11:58:03 AM by Bradley
Posted on 9/1/2007 6:10:47 AM by Soumen Datta
Posted on 1/28/2008 7:32:36 AM by Ivan
Posted on 5/23/2008 4:27:56 AM by alex
Posted on 11/5/2008 3:45:35 AM by syth
Posted on 5/25/2009 5:18:54 AM by James
Posted on 11/12/2009 5:12:37 PM by Delaforce Vintage Port
Posted on 11/14/2009 12:04:24 PM by Samuel De Rycke
Posted on 11/15/2009 9:40:48 PM by Shave A Beaver]cole kyla pussy shaved[/url] - matt
Posted on 12/3/2009 10:58:29 AM by Wwe Diva Upskirts
Posted on 1/28/2010 10:44:41 AM by psyco-simo
Posted on 3/4/2010 4:42:45 AM by jerome
Posted on 3/8/2010 7:11:14 PM by staff0rd
Posted on 6/1/2010 3:29:25 AM by Jagdeep Mankotia
Posted on 6/16/2010 8:02:15 AM by Pradeepta
Posted on 8/6/2010 5:15:58 AM by surya