declare @old varchar(100),@new varchar(100),@s varchar(8000) set @old='WY_tempdb' set @new='WY_BACKUP_DB' declare c cursor for select 'alter '+replace(stuff(a.[text],1,6,''),@old,@new) from syscomments a join sysobjects b on a.id=b.id and b.type='p' open c fetch c into @s while @@fetch_status=0 begin exec(@s) fetch c into @s end close c deallocate c
declare @old varchar(100),@new varchar(100),@s varchar(8000) set @old='WY_tempdb' set @new='WY_BACKUP_DB' declare c cursor for select 'alter '+replace(stuff(a.[text],1,6,''),@old,@new) from syscomments a join sysobjects b on a.id=b.id and b.type='p' and charindex(@old,a.[text])>0 open c fetch c into @s while @@fetch_status=0 begin exec(@s) fetch c into @s end close c deallocate c这个会快点
declare @old varchar(100),@new varchar(100),@s varchar(8000) set @old='from' set @new='FROM' declare c cursor for select replace(stuff(a.[text],charindex('CREATE',a.[text]),6,'ALTER'),@old,@new) + char(13)+'GO' from syscomments a join sysobjects b on a.id=b.id and b.type='p' and charindex(@old,a.[text])>0 open c fetch c into @s while @@fetch_status=0 begin print(@s) fetch c into @s end close c deallocate c
print(@s) EXEC(@S)--加这句会出错 fetch c into @s消息 102,级别 15,状态 1,过程 ExpansionTrackingBtsE1SP,第 59 行 'GO' 附近有语法错误。
如果是2005的,试试:declare @oldstr varchar(500),@newstr varchar(500),@proc_name varchar(500),@s varchar(max) set @old='WY_tempdb' set @new='WY_BACKUP_DB' declare c cursor for select distinct b.name from syscomments a join sysobjects b on a.id=b.id and b.type='p' and charindex(@oldstr,a.[text])>0 open c fetch c into @proc_name while @@fetch_status=0 begin set @s=null select @s=isnull(@s,'')+text from syscomments where object_name(id)=@proc_name set @s=replace(@s,@oldstr,@newstr) set @s=stuff(@s,charindex('CREATE',@s),6,'ALTER') exec(@s) fetch c into @proc_name end close c deallocate c
declare @old varchar(100),@new varchar(100),@s varchar(8000)
set @old='WY_tempdb'
set @new='WY_BACKUP_DB'
declare c cursor for
select 'alter '+replace(stuff(a.[text],1,6,''),@old,@new)
from syscomments a
join sysobjects b
on a.id=b.id and b.type='p'
open c
fetch c into @s
while @@fetch_status=0
begin
exec(@s)
fetch c into @s
end
close c
deallocate c
declare @old varchar(100),@new varchar(100),@s varchar(8000)
set @old='WY_tempdb'
set @new='WY_BACKUP_DB'
declare c cursor for
select 'alter '+replace(stuff(a.[text],1,6,''),@old,@new)
from syscomments a
join sysobjects b
on a.id=b.id and b.type='p' and charindex(@old,a.[text])>0
open c
fetch c into @s
while @@fetch_status=0
begin
exec(@s)
fetch c into @s
end
close c
deallocate c这个会快点
关键字 'CREATE' 附近有语法错误。
消息 111,级别 15,状态 1,第 14 行
'CREATE/ALTER PROCEDURE' 必须是查询批次中的第一个语句。
消息 102,级别 15,状态 1,第 105 行
'Sm' 附近有语法错误。
set @old='from'
set @new='FROM'
declare c cursor for
select replace(stuff(a.[text],charindex('CREATE',a.[text]),6,'ALTER'),@old,@new) + char(13)+'GO'
from syscomments a
join sysobjects b
on a.id=b.id and b.type='p' and charindex(@old,a.[text])>0
open c
fetch c into @s
while @@fetch_status=0
begin
print(@s)
fetch c into @s
end
close c
deallocate c
print(@s)
EXEC(@S)--加这句会出错
fetch c into @s消息 102,级别 15,状态 1,过程 ExpansionTrackingBtsE1SP,第 59 行
'GO' 附近有语法错误。
set @old='WY_tempdb'
set @new='WY_BACKUP_DB'
declare c cursor for
select distinct b.name
from syscomments a
join sysobjects b
on a.id=b.id and b.type='p' and charindex(@oldstr,a.[text])>0
open c
fetch c into @proc_name
while @@fetch_status=0
begin
set @s=null
select @s=isnull(@s,'')+text from syscomments where object_name(id)=@proc_name
set @s=replace(@s,@oldstr,@newstr)
set @s=stuff(@s,charindex('CREATE',@s),6,'ALTER')
exec(@s)
fetch c into @proc_name
end
close c
deallocate c
替换一下就行了。 htl写得很好了。