How to delete all stored procedures prefixed with "usp_" using SQL statements

ldqcom3000

I want to delete all stored procedures that start with the prefix USP_. How to write DROP PROCEDURE'usp_%' This is a syntax error. How to write it?

asurae

Select all stored procedure names beginning with USP_ and put them into the cursor.
declare c_1 cursor for select name from sysobjects where name like'USP_%' and xtype='P'
Traverse the cursor, create dynamic SQL statements, delete stored procedures
declare @pname nvarchar(100)
open c_1
fetch next from c_1 into @pname
while @@fetch_status=0
begin
    exec ('drop PROCEDURE'+@pname)
    fetch next from c_1 into @pname
end
close c_1
deallocate c_1

gaoqingfeng

If there are not many stored procedures after filtering
Try:
declare @str varchar(8000),@T_sql varchar(8000)
set @str=''
select @str=@str+','+name from sysobjects where xtype='P' and name like'usp_%'
select @str=stuff(@str,1,1,'')
set @T_sql='drop proc '
set @T_sql=@T_sql+replace(@str,',',' drop proc')+''
select @T_sql
exec(@T_sql)

pguppgdn

declare delcursor for select name from sysobjects where name like'usp_%' and xtype='P'
declare @pname nvarchar(100)
open delcursor
fetch next from delcursor into @pname
while @@fetch_status=0
begin
    exec ('drop PROCEDURE'+@pname)
    fetch next from delcursor into @pname
end
close delcursor
deallocate delcursor
go

mydriver

SELECT'DROP PROC'+name
FROM sysobjects
WHERE Name LIKE'usp_%'

--Then execute the printed result sentence by sentence

281711514hjl

Learned