三种方法:第一种针对单个表 第二种针对单个表字段太多的情况(print输出有字数限制) 第三种针对于单个库(最强模式可直接将库中所有表的所有字符串类型字段全部更新)
第一种:直接导出sql语句
declare @curTable varchar(500),
@FilterStr varchar(500);--过滤字符串 字符串及之后的数据将被替换为空
set @FilterStr='</title><style>.alx2{';
set @curTable='users_log_table';
DECLARE
@SQLList varchar(8000),
@tableName VARCHAR(1000),
@name VARCHAR(1000),
@type VARCHAR(1000);
set @SQLList='';
BEGIN
-- 定义游标.
DECLARE c_test_main CURSOR FAST_FORWARD FOR
-- select [dbid],[name] from sysdatabases where dbid>4;--查询数据库名称
select b.name as tableName,a.name as columnName,c.name as columnType from syscolumns a inner join sysobjects b on a.id=b.id inner join systypes c on a.xtype=c.xtype
WHERE a.id=OBJECT_ID(N'['+@curTable+']') And c.name not like 'sysname' ORDER BY colid
-- 打开游标.
OPEN c_test_main;
WHILE 1=1
BEGIN
-- 填充数据.
FETCH NEXT FROM c_test_main INTO @tableName,@name,@type;
-- 假如未检索到数据,退出循环.
IF @@fetch_status!= 0 BREAK;
if @type='varchar' or @type='nvarchar'
begin
--select @tableName,@name,@type;
set @SQLList=@SQLList+';update ['+@tableName+'] set ['+@name+']=replace(['+@name+'],isnull(substring(['+@name+'], case charindex('''+@FilterStr+''',['+@name+']) when 0 then len(['+@name+'])+1 else charindex('''+@FilterStr+''',['+@name+']) end ,len(['+@name+'])),''''),''''); '+(char(10)+char(13));
end
else if @type='ntext' or @type='text'
begin
set @SQLList=@SQLList+';update ['+@tableName+'] set ['+@name+']=cast(replace(cast(['+@name+'] as varchar(8000)), isnull(substring(cast(['+@name+'] as varchar(8000)),case charindex('''+@FilterStr+''',cast(['+@name+'] as varchar(8000))) when 0 then len(cast(['+@name+'] as varchar(8000)))+1 else charindex('''+@FilterStr+''',cast(['+@name+'] as varchar(8000))) end,len(cast(['+@name+'] as varchar(8000)))),''''),'''') as ntext ); '+(char(10)+char(13));
end
END;
-- 关闭游标
CLOSE c_test_main;
-- 释放游标.
DEALLOCATE c_test_main;
END
print @SQLList;