遍历替换整个数据库中所有用户表的字段中的数据

转载之 http://blog.csdn.net/flynetcn/archive/2007/05/17/1613875.aspx

 

DECLARE @table_name sysname
DECLARE @table_id int
DECLARE @strSource varchar(20),@strDest varchar(20)
set @strSource='Source   --要替换掉的字符串
set @strDest='Dest'           --用来替换的字符串
-- 获得所有User表
DECLARE tables_cursor CURSOR FOR select name,id from sysobjects where xtype='U'
OPEN tables_cursor
-- Perform the first fetch
FETCH NEXT FROM tables_cursor INTO @table_name,@table_id
-- Check @@FETCH_STATUS to see if there are any more rows to fetch.
WHILE @@FETCH_STATUS = 0
BEGIN
-- 获取表的文本类型字段
-- 'char','nchar','varchar','nvarchar','ntext','text'
DECLARE columns_cursor CURSOR FOR select name from syscolumns where id=@table_id and xtype in (select xtype from systypes where name='varchar')
DECLARE @column_name varchar(255)
OPEN columns_cursor
FETCH NEXT FROM columns_cursor INTO @column_name
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE @cmd varchar(8000)
-- 遍历表@table_name的每条记录的字段@column_name，更新该字段的内容
-- 动态生成执行语句
SET @cmd='
DECLARE @str_value varchar(8000)
DECLARE recorder_cursor CURSOR FOR select '+@column_name+' from '+@table_name+'
OPEN recorder_cursor
FETCH NEXT FROM recorder_cursor INTO @str_value
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT REPLACE(@str_value,'''+@strSource+''','''+@strDest+''')
UPDATE '+@table_name+' SET '+@column_name+'=@str_value WHERE CURRENT OF recorder_cursor
FETCH NEXT FROM recorder_cursor INTO @str_value
END
CLOSE recorder_cursor
DEALLOCATE recorder_cursor'
Print @cmd
--EXEC sp_executesql @cmd
FETCH NEXT FROM columns_cursor INTO @column_name
END
CLOSE columns_cursor
DEALLOCATE columns_cursor
-- This is executed as long as the previous fetch succeeds.
FETCH NEXT FROM tables_cursor INTO @table_name,@table_id
END
CLOSE tables_cursor
DEALLOCATE tables_cursor
------------------------------------------------------------------------------------------
csdn会员  zjcxc（邹建）
--1.替换一个表中的所有字段中的ABC为123  
declare   @s   nvarchar(4000),@tbname   sysname  
select    @s=N'',@tbname=N'jobs'     --要处理的表名  
select    @s=@s+N','+quotename(a.name)+N'=replace('+quotename(a.name)+N',N''ABC'',N''123'')'  
from   syscolumns   a,systypes   b  
where   a.id=object_id(@tbname)    
and   a.xusertype=b.xusertype  
and   b.name   like   N'%char'  
set   @s=stuff(@s,1,1,N'')  
exec(N'update   '+@tbname+'   set   '+@s)  
--2.替换当前数据库的所有表中的所有字段中的ABC为123  
exec   sp_msforeachtable   @command1=N'  
declare   @s   nvarchar(4000),@tbname   sysname  
select   @s=N'''',@tbname=N''?''  
select   @s=@s+N'',''+quotename(a.name)+N''=replace(''+quotename(a.name)+N'',N''''ABC'''',N''''123'''')''  
from   syscolumns   a,systypes   b  
where   a.id=object_id(@tbname)    
and   a.xusertype=b.xusertype  
and   b.name   like   N''%char''  
if   @@rowcount>0  
begin  
set   @s=stuff(@s,1,1,N'''')  
exec(N''update   ''+@tbname+''   set   ''+@s)  
end'  
---------------------------------------------------------------------------------
csdn会员 chuifengde(树上的鸟儿)
create proc procTab
@table varchar(100)
as
declare @t table(a int,b varchar(20))
declare @a varchar(1000)
insert @t select distinct b.colid, b.name from sysobjects a Inner Join syscolumns b On a.id=b.id Inner Join systypes c On b.xtype=c.xtype
where a.id=object_id(@table) and a.xtype='U' and c.xtype in(34,35,99,167,175,231,239) order by b.colid
select @a=coalesce(@a+',','')+b+'=isnull('+b+','''')' from @t
select @a='update '+@table+' set '+@a
exec(@a)
go
--如果更新整个库中的表则如下
sp_msforeachtable 'exec procTab ''?'' '
--更新某个表就
exec procTab '表名'
-------------------------------------------------------------------------