替換資料庫中 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'