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

12:01:00 am 0 Comments

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.

Unknown

0 comments:

Subscribe to: Post Comments (Atom)