当前位置: > 数据库 > SQL Server 2005 >

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

时间:2015-03-09 23:19来源:linux.it.net.cn 作者:IT

要实现类型为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')来更新。

源码如下:


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 = &apos;update &apos; @Table &apos; set &apos; @Column &apos;=replace(cast(&apos; @Column &apos; as varchar(8000)),@old,@new) where Datalength(&apos; @Column &apos;)<=8000&apos;;
        
EXECUTE sp_executesql @Sql,
                           N
&apos;@old varchar(100),@new varchar(100)&apos;,
                           
@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 = &apos;select     top 1 @offset = charindex(&apos;&apos;&apos; @old &apos;&apos;&apos; , &apos; @Column &apos;), @ptr = textptr(&apos; @Column &apos;) from &apos; @Table &apos; where Datalength(&apos; @Column &apos;)>8000 and &apos; @Column &apos; like &apos;&apos;%&apos; @old &apos;%&apos;&apos;&apos;;
            
EXEC sp_executesql @Sql,N&apos;@offset int OUTPUT,@ptr binary(16) OUTPUT,@old varchar(100)&apos;,
                               
@offset OUTPUT,@ptr OUTPUT,@old;

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

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

 


Create proc [dbo].[ReplaceKeyWord]
@old nvarchar(100),
@new nvarchar(100)
as
declare @sql nvarchar(1000)
set @sql=N&apos;  
declare   @s   nvarchar(4000),@tbname   sysname  
select   @s=N
&apos;&apos;&apos;&apos;,@tbname=N&apos;&apos;?&apos;&apos;  
select   @s=@s N
&apos;&apos;,&apos;&apos; quotename(a.name) N&apos;&apos;=replace(&apos;&apos; quotename(a.name) N&apos;&apos;,N&apos;&apos;&apos;&apos;&apos; @old &apos;&apos;&apos;&apos;&apos;,N&apos;&apos;&apos;&apos;&apos; @new &apos;&apos;&apos;&apos;&apos;)&apos;&apos;  
from   syscolumns   a,systypes   b  
where   a.id=object_id(@tbname)    
and   a.xusertype=b.xusertype  
and   b.name   like   N
&apos;&apos;%char&apos;&apos;  
if   @@rowcount>0  
begin  
set   @s=stuff(@s,1,1,N
&apos;&apos;&apos;&apos;)  
exec(N
&apos;&apos;update   &apos;&apos; @tbname &apos;&apos;   set   &apos;&apos; @s)  
end 
&apos;
--print @sql
exec   sp_msforeachtable   @sql;

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


使用方法如下:Exec ReplaceKeyWord &apos;www.megajoy.com&apos;,&apos;www.joy.cn&apos;

 

 

updatetext   表.列   指针   开始位置   去掉几位   插入的东西





(责任编辑:IT)
------分隔线----------------------------