DECLARE@table_namesysname
DECLARE@table_idint
DECLARE@strSourcevarchar(20),@strDestvarchar(20)
set@strSource='Source'--要替换掉的字符串
set@strDest='Dest'--用来替换的字符串

--获得所有User表
DECLAREtables_cursorCURSORFORselectname,idfromsysobjectswherextype='U'

OPENtables_cursor

--Performthefirstfetch
FETCHNEXTFROMtables_cursorINTO@table_name,@table_id

--Check@@FETCH_STATUStoseeifthereareanymorerowstofetch.
WHILE@@FETCH_STATUS=0
BEGIN

--获取表的文本类型字段
--'char','nchar','varchar','nvarchar','ntext','text'
DECLAREcolumns_cursorCURSORFORselectnamefromsyscolumnswhereid=@table_idandxtypein(selectxtypefromsystypeswherename='varchar')
DECLARE@column_namevarchar(255)

OPENcolumns_cursor

FETCHNEXTFROMcolumns_cursorINTO@column_name
WHILE@@FETCH_STATUS=0
BEGIN
DECLARE@cmdvarchar(8000)

--遍历表@table_name的每条记录的字段@column_name，更新该字段的内容
--动态生成执行语句
SET@cmd='
DECLARE@str_valuevarchar(8000)
DECLARErecorder_cursorCURSORFORselect'+@column_name+'from'+@table_name+'
OPENrecorder_cursor
FETCHNEXTFROMrecorder_cursorINTO@str_value
WHILE@@FETCH_STATUS=0
BEGIN
SELECTREPLACE(@str_value,'''+@strSource+''','''+@strDest+''')
UPDATE'+@table_name+'SET'+@column_name+'=@str_valueWHERECURRENTOFrecorder_cursor

FETCHNEXTFROMrecorder_cursorINTO@str_value
END
CLOSErecorder_cursor
DEALLOCATErecorder_cursor'

Print@cmd
--EXECsp_executesql@cmd

FETCHNEXTFROMcolumns_cursorINTO@column_name
END


CLOSEcolumns_cursor
DEALLOCATEcolumns_cursor

--Thisisexecutedaslongasthepreviousfetchsucceeds.
FETCHNEXTFROMtables_cursorINTO@table_name,@table_id
END

CLOSEtables_cursor
DEALLOCATEtables_cursor


------------------------------------------------------------------------------------------
csdn会员zjcxc（邹建）

--1.替换一个表中的所有字段中的ABC为123

declare@snvarchar(4000),@tbnamesysname
select@s=N'',@tbname=N'jobs'--要处理的表名
select@s=@s+N','+quotename(a.name)+N'=replace('+quotename(a.name)+N',N''ABC'',N''123'')'
fromsyscolumnsa,systypesb
wherea.id=object_id(@tbname)
anda.xusertype=b.xusertype
andb.namelikeN'%char'
set@s=stuff(@s,1,1,N'')
exec(N'update'+@tbname+'set'+@s)

--2.替换当前数据库的所有表中的所有字段中的ABC为123
execsp_msforeachtable@command1=N'
declare@snvarchar(4000),@tbnamesysname
select@s=N'''',@tbname=N''?''
select@s=@s+N'',''+quotename(a.name)+N''=replace(''+quotename(a.name)+N'',N''''ABC'''',N''''123'''')''
fromsyscolumnsa,systypesb
wherea.id=object_id(@tbname)
anda.xusertype=b.xusertype
andb.namelikeN''%char''
if@@rowcount>0
begin
set@s=stuff(@s,1,1,N'''')
exec(N''update''+@tbname+''set''+@s)
end'
---------------------------------------------------------------------------------
csdn会员chuifengde(树上的鸟儿)

createprocprocTab
@tablevarchar(100)
as
declare@ttable(aint,bvarchar(20))
declare@avarchar(1000)

insert@tselectdistinctb.colid,b.namefromsysobjectsaInnerJoinsyscolumnsbOna.id=b.idInnerJoinsystypescOnb.xtype=c.xtype
wherea.id=object_id(@table)anda.xtype='U'andc.xtypein(34,35,99,167,175,231,239)orderbyb.colid

select@a=coalesce(@a+',','')+b+'=isnull('+b+','''')'from@t
select@a='update'+@table+'set'+@a

exec(@a)

go
--如果更新整个库中的表则如下
sp_msforeachtable'execprocTab''?'''
--更新某个表就
execprocTab'表名'
-------------------------------------------------------------------------

原文链接 : https://blog.csdn.net/flynetcn/article/details/1613875