替换数据库中 varchar.nvarchar,ntext,text等 的关键字（转）

替换数据库中 varchar.nvarchar,ntext,text等 的关键字（转）

最近因为域名www.aaa.com要换成www.bbb.cn
所以我写了两个存储过程，实现了一次性替换数据库中所有表所有列的关键字，包括了数据表中的所有文本字段(varchar.nvarchar,ntext,text等)..
当然要实现类型为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+')<=8000';
        EXECUTE sp_executesql @Sql,
                           N'@old varchar(100),@new varchar(100)',
                           @old,
                           @new
        declare @ptr binary(16) ,@offset int,@dellen int
       
        set @dellen = len(@old)
       
        set @offset = 1
        while @offset>=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
go
使用方法如下：Exec ReplaceKeyWord 'www.aaa.com','www.bbb.cn'

posted on 2008-12-28 17:14

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

http://cn.daxia123.cn/cn.js
http://cn.jxmmtv.com/cn.js