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

 

吾阅：待验证；

 

 

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

 

转自：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