将所有USP_开头的存储过程名线选出来,放入游标。 declare c_1 cursor for select name from sysobjects where name like 'USP_%' and xtype='P' 遍历游标,创建动态SQL语句,删除存储过程 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
如果过滤后的存储过程不是很多的话 试试: 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)
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
SELECT 'DROP PROC '+name FROM sysobjects WHERE Name LIKE 'usp_%'--然后把打印出来的结果一句句执行
declare c_1 cursor for select name from sysobjects where name like 'USP_%' and xtype='P'
遍历游标,创建动态SQL语句,删除存储过程
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
试试:
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)
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
FROM sysobjects
WHERE Name LIKE 'usp_%'--然后把打印出来的结果一句句执行