use masterdeclare @sp as varchar(100) while exists(select 1 from sysobjects where type='P' and name not like 'sp_%' and name not like 'xp_%' and name <> 'MS_sqlctrs_users') begin set @sp = (select top 1 name from sysobjects where type='P' and name not like 'sp_%' and name not like 'xp_%' and name <> 'MS_sqlctrs_users') while not exists(select 1 from hh where name = @sp) --你HH表里面保存的字段假设为name exec('drop procedure ' + @sp) end
create table tb (proc_name varchar(120)) insert into tb select 'pr_add_goods' declare @count int,@name varchar(120),@sql varchar(2000) select @count=COUNT(1) from tb while @count>0 begin select top(1) @name =proc_name from tb set @sql=' drop procedure' +@name exec (@sql) set @count =@count -1 end
更改一下:use masterdeclare @sp as varchar(100) while exists(select 1 from sysobjects where type='P' and name not like 'sp_%' and name not like 'xp_%' and name <> 'MS_sqlctrs_users') begin set @sp = (select top 1 name from sysobjects where type='P' and name not like 'sp_%' and name not like 'xp_%' and name <> 'MS_sqlctrs_users') if not exists(select 1 from hh where name = @sp) --你HH表里面保存的字段假设为name exec('drop procedure ' + @sp) end
use masterdeclare @sp as varchar(100) while exists(select 1 from sysobjects where type='P' and name not like 'sp_%' and name not like 'xp_%' and name <> 'MS_sqlctrs_users') begin set @sp = (select top 1 name from sysobjects where type='P' and name not like 'sp_%' and name not like 'xp_%' and name <> 'MS_sqlctrs_users') if not exists(select 1 from hh where name = @sp) --你HH表里面保存的字段假设为name exec('drop procedure ' + @sp) end把上面的use master去掉.在你的当前库中去运行.declare @sp as varchar(100) while exists(select 1 from sysobjects where type='P' and name not like 'sp_%' and name not like 'xp_%' and name <> 'MS_sqlctrs_users') begin set @sp = (select top 1 name from sysobjects where type='P' and name not like 'sp_%' and name not like 'xp_%' and name <> 'MS_sqlctrs_users') if not exists(select 1 from hh where name = @sp) --你HH表里面保存的字段假设为name exec('drop procedure ' + @sp) end
那就奇怪了.不会你有其他在使用某些存储过程吧?如果是,建议断开那些连接.或者你如果能制定某些相同的存储过程,例如某几个字符号开头的,先试试. declare @sp as varchar(100) while exists(select 1 from sysobjects where type='P' and name like 'abce...') begin set @sp = (select top 1 name from sysobjects where type='P' and name like 'abce...') if not exists(select 1 from hh where name = @sp) --你HH表里面保存的字段假设为name exec('drop procedure ' + @sp) end
动态也没有让你一个一个的删除啊??
你从系统表里得到那些不需要删除的过程的名字,然后再拼接成exec('drop proc '+我得到的名字)
while exists(select 1 from sysobjects where type='P' and name not like 'sp_%' and name not like 'xp_%' and name <> 'MS_sqlctrs_users')
begin
set @sp = (select top 1 name from sysobjects where type='P' and name not like 'sp_%' and name not like 'xp_%' and name <> 'MS_sqlctrs_users')
while not exists(select 1 from hh where name = @sp) --你HH表里面保存的字段假设为name
exec('drop procedure ' + @sp)
end
create table tb (proc_name varchar(120))
insert into tb select 'pr_add_goods'
declare @count int,@name varchar(120),@sql varchar(2000)
select @count=COUNT(1) from tb
while @count>0
begin
select top(1) @name =proc_name from tb
set @sql=' drop procedure' +@name
exec (@sql)
set @count =@count -1
end
while exists(select 1 from sysobjects where type='P' and name not like 'sp_%' and name not like 'xp_%' and name <> 'MS_sqlctrs_users')
begin
set @sp = (select top 1 name from sysobjects where type='P' and name not like 'sp_%' and name not like 'xp_%' and name <> 'MS_sqlctrs_users')
if not exists(select 1 from hh where name = @sp) --你HH表里面保存的字段假设为name
exec('drop procedure ' + @sp)
end
use masterdeclare @sp as varchar(100)
while exists(select 1 from sysobjects where type='P' and name not like 'sp_%' and name not like 'xp_%' and name <> 'MS_sqlctrs_users')
begin
set @sp = (select top 1 name from sysobjects where type='P' and name not like 'sp_%' and name not like 'xp_%' and name <> 'MS_sqlctrs_users')
if not exists(select 1 from hh where name = @sp) --你HH表里面保存的字段假设为name
exec('drop procedure ' + @sp)
end把上面的use master去掉.在你的当前库中去运行.declare @sp as varchar(100)
while exists(select 1 from sysobjects where type='P' and name not like 'sp_%' and name not like 'xp_%' and name <> 'MS_sqlctrs_users')
begin
set @sp = (select top 1 name from sysobjects where type='P' and name not like 'sp_%' and name not like 'xp_%' and name <> 'MS_sqlctrs_users')
if not exists(select 1 from hh where name = @sp) --你HH表里面保存的字段假设为name
exec('drop procedure ' + @sp)
end
declare @sp as varchar(100)
while exists(select 1 from sysobjects where type='P' and name like 'abce...')
begin
set @sp = (select top 1 name from sysobjects where type='P' and name like 'abce...')
if not exists(select 1 from hh where name = @sp) --你HH表里面保存的字段假设为name
exec('drop procedure ' + @sp)
end