Search - Articles
DevASP.NET for ASP.NET, VB.NET, XML and C# (C-Sharp) Developers Tuesday, March 03, 2009
Dev Articles
Search Directory
ASP.NET
VB.Net
C-Sharp
SQL Server
 

Delete All Procedures from a database using a Stored procedure in SQL Server

Author: Aadil

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

 

Be very careful when calling this stored procedure. It’s really useful for special purposes and not for every day use.

Article Comments
quite helpful stored procedure,
Thanks

Posted on 5/18/2007 1:23:49 PM by Manish Kaushik

Exactly what I was looking for. Works like a charm. Thanks!

Posted on 6/25/2007 11:58:03 AM by Bradley

It's good. But you can do it in different ways also.

Posted on 9/1/2007 6:10:47 AM by Soumen Datta

Great sp ! You saved me a lot of time :)

Posted on 1/28/2008 7:32:36 AM by Ivan

nice, thanks a lot :)

Posted on 5/23/2008 4:27:56 AM by alex

I am getting error
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '-'.

while i do
exec dbo.DeleteAllProcedures

Posted on 11/5/2008 3:45:35 AM by syth

Thank's alot for this posting it has helped me alot.

Posted on 5/25/2009 5:18:54 AM by James

any more posts coming ?

Posted on 11/12/2009 5:12:37 PM by Delaforce Vintage Port

Thank you. Only usefull article I could find about this (in a short timespan) and solves the problem perfectly.

Posted on 11/14/2009 12:04:24 PM by Samuel De Rycke

yo.. luv this post )

Posted on 11/15/2009 9:40:48 PM by Shave A Beaver]cole kyla pussy shaved[/url] - matt

huh.. thanks for thoughts :)) b

Posted on 12/3/2009 10:58:29 AM by Wwe Diva Upskirts

here is my version, thanks

USE [db_name]

CREATE PROCEDURE dbo.__DeleteAllProcedures
As
declare @procName varchar(500)

-- Removes stored procedures
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

-- Removes Views
declare cur cursor
for select [name] from sys.objects where [type] = 'v'
open cur
fetch next from cur into @procName
while @@fetch_status = 0
begin
exec('drop view ' + @procName)
fetch next from cur into @procName
end
close cur
deallocate cur

-- Removes Functions
declare cur cursor
for select [name] from sys.objects WHERE [type] = 'fn'
open cur
fetch next from cur into @procName
while @@fetch_status = 0
begin
exec('drop function ' + @procName)
fetch next from cur into @procName
end
close cur
deallocate cur

-- removes itselfs
DROP PROCEDURE __DeleteAllProcedures

Go

exec __DeleteAllProcedures

Posted on 1/28/2010 10:44:41 AM by psyco-simo

Hai Thanks a ton.... It saved my database cleaning time

Posted on 3/4/2010 4:42:45 AM by jerome

thanks alot for this

Posted on 3/8/2010 7:11:14 PM by staff0rd

Thanks Bro, Its working Fine.

Its very easy to understand. helpfull.

= Jagdeep Mankotia

Posted on 6/1/2010 3:29:25 AM by Jagdeep Mankotia

Simply great work

Posted on 6/16/2010 8:02:15 AM by Pradeepta

Great one my mouse thanked you a lot..........

Posted on 8/6/2010 5:15:58 AM by surya

   
Add Article Comment:
Name :
Email Address :
   
Comments :
 
   
<< How to dynamically change the executing SQL in a stored procedure in Microsoft SQL Server

Disclaimer - Privacy
© 2002-2017 DevASP.net