delete stored procs

I need some SQL to delete a bunch (300) of stored procs.  The names of the procs all begin with the same string.  

Thanks in advance
LVL 1
HLRosenbergerAsked:
Who is Participating?
 
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
to start with, check this query:
SELECT 'DROP PROCEDURE ' + name + ';'
FROM sys.procedures
where [name] like 'abc%' 

Open in new window


copy/paste that output into a sql command window and run it
0
 
sqlxlCommented:
Open a cursor that collects all the procs beginning with that string.

Then use dynamic SQL to execute a 'drop proc' command for each.
0
 
itmarsolutionsCommented:
declare @name varchar(4000)
declare c cursor for
select name from sysobjects where xtype='p' and name like '%sp_'
open c
fetch next from c into @name
while @@fetch_status=0
begin
print @name
--your drop statement

fetch next from c into @name
end
close c
deallocate c
0
 
HLRosenbergerAuthor Commented:
Terrific!  Thanks!
0
All Courses

From novice to tech pro — start learning today.