Tuesday, March 8, 2011

How To Drop All Stored Procedures In Your Database When Using Sql Server

Ever have the need to drop all of the stored procedures in your database? Well if you do, you are in luck! Here is a script that will drop all of the stored procedures in your Sql Server database for you. Just open a new query window, enter the script, type your database name in the Use statement and press F5!


USE YourDatabaseNameHere
GO

declare @storedProcedureName sysname

declare procCursor cursor for
select name from sysobjects where type = 'P' and objectproperty(id, 'IsMSShipped') = 0

open procCursor
fetch next from procCursor into @storedProcedureName
while @@FETCH_STATUS = 0
begin
exec('drop proc ' + @storedProcedureName)
fetch next from procCursor into @storedProcedureName
end

close procCursor
deallocate procCursor
go

That is it! Now you can delete all of the stored procedures in your database quickly and easily!

Smooches,

Kila

1 comment:

Anonymous said...

With the help of SQL Server Management Studio or Transact-SQL, we can grant permissions on a single or multiple stored procedure in SQL. These permissions can be granted to an existing user, database role, or application role in the database.
Grant Permissions on a Stored Procedure