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

因为域名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
1Create
proc
[dbo].[UpdateTextColumn]
2@Table
varchar(100),
3@Columns
varchar(200),
4@old
varchar(100),
5@new
varchar(100)
6as
7    set nocount on
8    declare
@sql
nvarchar(2000)
9    declare
@Column
varchar(50)
10    declare
@cpos
int,@npos
int
11    declare
@curoffset
int
12    set
@cpos=1;
13    set
@npos=1;
14    set
@npos=charindex(',',@Columns,@cpos);
15    set
@curoffset=0;
16
17    while(@npos>0)
18    begin
19        set
@Column
=
substring(@Columns,@cpos,@npos-@cpos);
20        set
@cpos
=
@npos+1
21        set
@npos=charindex(',',@Columns,@cpos);
22        
23        set
@sql
=
'update '+@Table+' set '+@Column+'=replace(cast('+@Column+' as varchar(8000)),@old,@new) where Datalength('+@Column+')<=8000';
24        EXECUTE sp_executesql @Sql,
25                           N'@old varchar(100),@new varchar(100)',
26                           @old,
27                           @new
28        declare
@ptr
binary(16) ,@offset
int,@dellen
int
29        
30        set
@dellen
=
len(@old)
31        
32        set
@offset
=
1
33        while
@offset>=1
34        begin
35            if
@curoffset<>0
36               set
@curoffset=@offset;
37            set
@offset
=
0
38            set
@sql
=
'select     top 1 @offset = PATINDEX(''%'+@old+'%'' , '+@Column+'), @ptr = textptr('+@Column+') from '+@Table+' where Datalength('+@Column+')>8000 and '+@Column+' like ''%'+@old+'%''';
39            EXEC sp_executesql @Sql,N'@offset int OUTPUT,@ptr binary(16) OUTPUT,@old varchar(100),@curoffset int',
40                               @offset OUTPUT,@ptr OUTPUT,@old,@curoffset;
41
42           if (@offset
is
not
null) and (@offset
>
0)
43            begin
44                set
@offset
=
@offset-1
45
46                set
@sql='updatetext '+@Table+'.'+@Column+' @ptr @offset @dellen @new';
47                EXEC sp_executesql @Sql,N'@offset int ,@ptr binary(16),@dellen int,@new varchar(100)',@offset,@ptr,@dellen,@new;
48            end
49        end
50end

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.megajoy.com','www.joy.cn'
以上是全部源代码，如果您发现了错误，欢迎指出，我会在第一时间修改。如果您有更好的解决方案，也欢迎共享，谢谢！！

补充：
如果是mssql2005版本,您只需要如下代码：
MSSQL2005
1declare
@sql
nvarchar(1000)
2declare
@old
nvarchar(100)
3declare
@new
nvarchar(100)
4set
@old
='megagggjoy.com'
5set
@new
='joy.cn'
6set
@sql=N'
7declare   @s   nvarchar(4000),@tbname   sysname  
8select   @s=N'''',@tbname=N''?''
9select   @s=@s+N'',''+quotename(a.name)+N''=replace(''+quotename(a.name)+N'',N'''''+@old+''''',N'''''+@new+''''')''
10from   syscolumns   a,systypes   b  
11where   a.id=object_id(@tbname)   
12and   a.xusertype=b.xusertype  
13and   b.name   like   N''%char''
14if   @@rowcount>0  
15begin  
16set   @s=stuff(@s,1,1,N'''')  
17exec(N''update   ''+@tbname+''   set   ''+@s)  
18end '
19--print @sql
20exec   sp_msforeachtable   @sql;
21
22set
@sql=N'
23declare   @s   nvarchar(4000),@tbname   sysname  
24select   @s=N'''',@tbname=N''?''
25select   @s=@s+N'',''+quotename(a.name)+N''=replace(cast(''+quotename(a.name) +N''as varchar(max)),N'''''+@old+''''',N'''''+@new+''''')''
26from   syscolumns   a,systypes   b  
27where   a.id=object_id(@tbname)   
28and   a.xusertype=b.xusertype  
29and   b.name   like   N''%text''
30print  @s
31if   @@rowcount>0  
32begin  
33set   @s=stuff(@s,1,1,N'''')  
34exec(N''update   ''+@tbname+''   set   ''+@s)  
35end '
36exec   sp_msforeachtable @sql

 

Create proc [dbo].[UpdateTextColumn]

 

 

@Table varchar(100),

 

 

@Columns varchar(200),

 

 

@old varchar(100),

 

 

@new varchar(100)

 

 

     as

 

 

        set nocount on

 

 

        declare @sql nvarchar(2000)

 

 

        declare @Column varchar(50)

 

 

        declare @cpos int,@npos int

 

 

        declare @curoffset int

 

 

        set @cpos=1;

 

 

        set @npos=1;

 

 

        set @npos=charindex(',',@Columns,@cpos);

 

 

        set @curoffset=0;

 

 

 

 

        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

 

 

                if @curoffset<>0

 

 

                   set @curoffset=@offset;

 

 

                set @offset = 0

 

 

                set @sql = 'select     top 1 @offset = PATINDEX(''%'+@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),@curoffset int',

 

 

@offset OUTPUT,@ptr OUTPUT,@old,@curoffset;

 

 

 

 

               if (@offset is not null) and (@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

 

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