declare @objectName as sysname, @definition as nvarchar(max), @sourceStr as nvarchar(100), @replaceStr as nvarchar(100) set @sourceStr='testString1';--要被替换的字符串,换成你自己的。 set @replaceStr='testString2';--替换字符串,换成你自己的。
select OBJECT_NAME(object_id) as objName,definition into #tempObj from sys.sql_modules;declare cReplacedObj cursor fast_forward for select objName,definition from #tempObj;
open cReplacedObj fetch next from cReplacedObj into @objectName,@definition; while @@FETCH_STATUS=0 begin set @definition=replace(@definition,@sourceStr,@replaceStr);--替换操作。 set @definition=N'alter'+substring(@definition,CHARINDEX(' ',@definition),len(@definition));--动态生成alter语句,重新定义相关的函数,存贮过程或触发器。 exec(@definition);--执行alter语句。 fetch next from cReplacedObj into @objectName,@definition; end close cReplacedObj; deallocate cReplacedObj;
如何批量将代码中的该字符串替换特定的字符串?
比如一个字符串在多个存储过程/函数/触发器都存在,如何它全部用另一指定符串替换.
是不是直接将syscomments中的text栏位的内容进行替换就可以了?
declare
@objectName as sysname,
@definition as nvarchar(max),
@sourceStr as nvarchar(100),
@replaceStr as nvarchar(100)
set @sourceStr='testString1';--要被替换的字符串,换成你自己的。
set @replaceStr='testString2';--替换字符串,换成你自己的。
select OBJECT_NAME(object_id) as objName,definition
into #tempObj
from sys.sql_modules;declare cReplacedObj cursor fast_forward for
select objName,definition
from #tempObj;
open cReplacedObj
fetch next from cReplacedObj into @objectName,@definition;
while @@FETCH_STATUS=0
begin
set @definition=replace(@definition,@sourceStr,@replaceStr);--替换操作。
set @definition=N'alter'+substring(@definition,CHARINDEX(' ',@definition),len(@definition));--动态生成alter语句,重新定义相关的函数,存贮过程或触发器。
exec(@definition);--执行alter语句。
fetch next from cReplacedObj into @objectName,@definition;
end
close cReplacedObj;
deallocate cReplacedObj;