如果列类型都是字符型: ---------------------------------------------------------------- declare @s varchar(8000) set @s = '' select @s = @s + ',' + name +'=replace('+name+', '' '','''')' from syscolumns where id = object_id('表名')
set @s = 'update 表名 set ' + stuff(@s,1,1,'') exec(@s)
--替换数据库中所有的表 --如果指定替换的表,稍微修改下,就可以了!--详细参考: http://blog.csdn.net/zlp321002/archive/2005/06/16/395621.aspx 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
----------------------------------------------------------------
declare @s varchar(8000)
set @s = ''
select
@s = @s + ',' + name +'=replace('+name+', '' '','''')'
from
syscolumns
where
id = object_id('表名')
set @s = 'update 表名 set ' + stuff(@s,1,1,'')
exec(@s)
--如果指定替换的表,稍微修改下,就可以了!--详细参考:
http://blog.csdn.net/zlp321002/archive/2005/06/16/395621.aspx
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