?

?

?

问题描述：将数据库里所有表（用户表）的某一字段值的某个字，改为另外一个字；

?

解决方法：使用Replace函数即可；外加游标实现；-- mssql 2005试验通过

?

?

?

?

最新版， 只需更改一处即可；

declare?? @oldstr?? varchar(100)
set?? @oldstr= '里'???? --原字符
declare?? @newstr?? varchar(100)
set?? @newstr= '裏'???? --新字符

declare?? @s?? varchar(8000)
declare?? tb?? cursor?? local?? for
select?? s= 'if?? exists(select?? 1?? from?? ['+b.name+ ']?? where?? ['+a.name+ ']?? like?? ''%'+@oldstr+ '%'' )
update?? ['+b.name+ ']?? set?? ['+a.name+ ']= Replace('+a.name+','''+@oldstr+''','''+@newstr+''')'+ '?? where?? ['+a.name+ '] like ''%'+@oldstr+ '%'''
from?? syscolumns?? a?? join?? sysobjects?? b?? on?? a.id=b.id
where?? b.xtype= 'U'?? and?? a.status> =0
? and?? a.xusertype?? in(175,239,231,167)
open?? tb
fetch?? next?? from?? tb?? into?? @s
while?? @@fetch_status=0
begin
? exec(@s)
? print @s

? fetch?? next?? from?? tb?? into?? @s
end
close?? tb
deallocate?? tb

?

?

?

?

?

?

?

?

---------------------------------------------

要更改两处；?

?

declare?? @oldstr?? varchar(100)
set?? @oldstr= '恆'???? --原字符
declare?? @newstr?? varchar(100)
set?? @newstr= '恒'???? --新字符

declare?? @s?? varchar(8000)
declare?? tb?? cursor?? local?? for
select?? s= 'if?? exists(select?? 1?? from?? ['+b.name+ ']?? where?? ['+a.name+ ']?? like?? ''%'+@oldstr+ '%'' )
update?? ['+b.name+ ']?? set?? ['+a.name+ ']= Replace('+a.name+',''恆'',''恒'')'+ '?? where?? ['+a.name+ '] like ''%'+@oldstr+ '%'''
from?? syscolumns?? a?? join?? sysobjects?? b?? on?? a.id=b.id
where?? b.xtype= 'U'?? and?? a.status> =0
? and?? a.xusertype?? in(175,239,231,167)
open?? tb
fetch?? next?? from?? tb?? into?? @s
while?? @@fetch_status=0
begin
? exec(@s)
? print @s

? fetch?? next?? from?? tb?? into?? @s
end
close?? tb
deallocate?? tb