2008年05月15日 19:12:47
一次性替换数据库中所有表所有列的关键字
|
一次性替换数据库中所有表所有列的关键字 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 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 Tags：
sql 2005
|
一共有 2 条评论