一次性替换数据库中所有表所有列的关键字

gujideshatan

一次性替换数据库中所有表所有列的关键字

要实现类型为varchar,nvarchar的字段是很容易实现的，用一句SQL就可以搞定：
update Table set Column=Replace(Column,'oldkeyword','newkeyword').

但如果你用这句SQL语句去更新类型为text,ntext的字段是就要报错了：
err info:消息 8116，级别 16，状态 1，第 1 行
参数数据类型 text 对于 replace 函数的参数 1 无效。

这里也许有人会想到，可以先把text,ntext类型转换成varchar,nvarchar来实现，SQL语句如下：
update Table set Column=Replace(Cast(Column as varchar(8000)),'oldkeyword','newkeyword')
但是，你想过没有，如果ntext,text类型的列里，已存放的数据大于8000字节的话，你的数据就会被丢失了。所有，你要慎用！！

不过还好，MS提供了updatetext(使用 UPDATETEXT 可以只更改 text、ntext 或 image 列的一部分).

如果你要查看updatetext的用法，请查看SQL联机帮助丛书.

费话不多说了，下面我简单的介绍一下我的解决方案以及实现的关键技术.

1:sp_msforeachtable 用来loop表中的所有列
2:更新类型为ntext,text类型的列时，先判断DATALENGTH(Column)是否大于8000字节，如果小于8000字节的话，我们可以使用
? update Table set Column=Replace(Cast(Column as varchar(8000)),'oldkeyword','newkeyword')来更新。

源码如下：

UpdateTextColumn
Create proc [dbo].[UpdateTextColumn]
@Table varchar(100),
@Columns varchar(200),--eg:Column1,Column2,
@old varchar(100),
@new varchar(100)
as
? set nocount on
? declare @sql nvarchar(2000)
? declare @Column varchar(50)
? declare @cpos int,@npos int
? set @cpos=1;
? set @npos=1;
? set @npos=charindex(',',@Columns,@cpos);

? while(@npos>0)
? begin
? ? set @Column = substring(@Columns,@cpos,@npos-@cpos);
? ? set @cpos = @npos 1
? ? set @npos=charindex(',',@Columns,@cpos);
? ?
? ? set @sql = 'update ' @Table ' set ' @Column '=replace(cast(' @Column ' as varchar(8000)),@old,@new) where Datalength(' @Column ')=1
? ? begin
? ? ? ? set @offset = 0
? ? ? ? set @sql = 'select ? top 1 @offset = charindex(''' @old ''' , ' @Column '), @ptr = textptr(' @Column ') from ' @Table ' where Datalength(' @Column ')>8000 and ' @Column ' like ''%' @old '%''';
? ? ? ? EXEC sp_executesql @Sql,N'@offset int OUTPUT,@ptr binary(16) OUTPUT,@old varchar(100)',
? ? ? ? ? ? ? ? ? ? @offset OUTPUT,@ptr OUTPUT,@old;

? ? ? if @offset > 0
? ? ? ? begin
? ? ? ? ? set @offset = @offset-1

? ? ? ? ? set @sql='updatetext ' @Table '.' @Column ' @ptr @offset @dellen @new';
? ? ? ? ? EXEC sp_executesql @Sql,N'@offset int ,@ptr binary(16),@dellen int,@new varchar(100)',@offset,@ptr,@dellen,@new;
? ? ? ? end
? ? end
end
go

ReplaceKeyWord
Create proc [dbo].[ReplaceKeyWord]
@old nvarchar(100),
@new nvarchar(100)
as
declare @sql nvarchar(1000)
set @sql=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''''' @old ''''',N''''' @new ''''')''
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 '
--print @sql
exec ? sp_msforeachtable ? @sql;

set @sql=N'
declare ? @s ? nvarchar(4000),@tbname ? sysname
select ? @s=N'''',@tbname=N''?''
select ? @s=@s quotename(a.name) N'',''
from ? syscolumns ? a,systypes ? b
where ? a.id=object_id(@tbname) ?
and ? a.xusertype=b.xusertype
and ? b.name ? like ? N''%text''
if ? @@rowcount>0
begin
exec UpdateTextColumn @tbname,@s,''' @old ''',''' @new '''
end
' ;
exec ? sp_msforeachtable @sql