如何遍历所有表，修改每个表中这一项

?

吾阅：待验证；

?

?

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

?

转自：http://topic.csdn.net/t/20050901/13/4244285.html

?

?

--替换所有与该字段值，相关的表！??
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+ ']= ' ' '+@newstr+ ' ' '?? where?? [ '+a.name+ ']= ' ' '+@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)
? fetch?? next?? from?? tb?? into?? @s
end
close?? tb
deallocate?? tb

?

?

----------------------------------------------------
----查找出所有包括列名CODE的表：
select??
??????? distinct?? a.name??
from??
??????? sysobjects?? a,syscolumns?? b??
where??
??????? a.id?? =?? b.id?? and?? b.name=N 'CODE '

?

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

?

?

----------给所有需要大量修改的人
-------------------------------------------查看需要修改的项--------------------------
select ? * ? from ? T_Currency


--------------------关闭符合修改条件的所有触发器---------------------------
declare ? @s ? varchar(8000)
declare ? tb ? cursor ? local ? for
select ? s= 'if ? exists(select ? 1 ? from ? [ '+b.name+ '])
? alter ? table ? [ '+b.name+ '] ? disable ? trigger ? all ? '
from ? syscolumns ? a ? join ? sysobjects ? b ? on ? a.id=b.id
where ? b.xtype= 'U ' ? and ? a.name=N 'CurrCode ' ? 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
----------print ? @s
exec(@s)
? fetch ? next ? from ? tb ? into ? @s
end
close ? tb
deallocate ? tb
----------------------------------------------改CurrCode内容,需输入参数，但不会改Currency表
declare ? @oldstr ? varchar(100)
set ? @oldstr= '033 ' ? ? --原字符
declare ? @newstr ? varchar(100)
set ? @newstr= '003 ' ? ? --新字符


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+ ']= ' ' '+@newstr+ ' ' ' ? where ? [ '+a.name+ ']= ' ' '+@oldstr+ ' ' ' ? '
from ? syscolumns ? a ? join ? sysobjects ? b ? on ? a.id=b.id
where ? b.name <> 'T_Currency ' ? and ? b.xtype= 'U ' ? and ? a.name=N 'CurrCode ' ? 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
-------------print ? @s
exec(@s)
? fetch ? next ? from ? tb ? into ? @s
end
close ? tb
deallocate ? tb
-------------------------------------开启所有触发器
declare ? tb ? cursor ? local ? for
select ? s= 'if ? exists(select ? 1 ? from ? [ '+b.name+ '])
? alter ? table ? [ '+b.name+ '] ? enable ? trigger ? all ? '
from ? syscolumns ? a ? join ? sysobjects ? b ? on ? a.id=b.id
where ? ? b.xtype= 'U ' ? and ? a.name=N 'CurrCode ' ? 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
----------print ? @s
exec(@s)
? fetch ? next ? from ? tb ? into ? @s
end
close ? tb
deallocate ? tb
--------------------------
select ? CurrCode ? from ? T_PO

?

?

?

?

?

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

?

--try 改良版
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+ ']= ' ' '+@newstr+ ' ' ' ? where ? [ '+a.name+ ']= ' ' '+@oldstr+ ' ' ' ? '
from ? syscolumns ? a ? join ? sysobjects ? b ? on ? a.id=b.id
where ? b.xtype= 'U ' ? and ? a.name=N 'ILOVEYOU ' ? and ? a.status> =0 ? ? ? --注意：列名为: "ILOVEYOU "
? and ? a.xusertype ? in(175,239,231,167)
open ? tb
fetch ? next ? from ? tb ? into ? @s
while ? @@fetch_status=0
begin
? exec(@s)
? fetch ? next ? from ? tb ? into ? @s
end
close ? tb
deallocate ? tb

?

?