Code Simplified – Viral Sarvaiya

Code Simplified – Viral Sarvaiya, Web Developer Friends, dot net Developer, Sql Server Developer

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

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: