How to delete all stored procedures
This a example to delete all stored procedures and delete all the tables from a database. Here cursor is used to fecth the consecutive objects and tables from the database. In this example you can also get an example Query for the cursor implementaition.
How to Delete all Stored Procedures
//declare a variable to store procedure name
declare @name varchar (100)
//declare cursor variable to get output from cursor
declare @cur cursor
set @cur = cursor for
select [name] from sys.objects where type = 'p'
//open cursor and fetch the value then drop the procedure
open @cur
fetch next from @cur into @name
while @@FETCH_STATUS = 0
begin
exec('drop proc '+ @name)
fetch next from @cur into @name
end
close @cur
deallocate @curHow to Delete all Tables
//same as deleting stored procedure we can delete tables.
//declare a variable to table name
declare @tbl varchar (100)
//declare cursor variable to get output from cursor
declare @tabcur cursor
set @tabcur = CURSOR FOR
select [name] from sys.tables
//open cursor and fetch the value then drop the table
open @tabcur
fetch next from @tabcur into @tbl
while @@FETCH_STATUS = 0
begin
fetch next from @tabcur into @tbl
exec('drop table '+ @tbl)
end
close @tabcur
deallocate @tabcur
Dear All,
check below query
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[MySP]')
AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[MySP]
regards,
marudhu...