先备份数据库,然后试试这个:declare @t varchar(555),@c varchar(555) ,@inScript varchar(8000) set @inScript=' ' --改为' ',char(13),char(10),char(9) 执行4次。 declare table_cursor cursor for select a.name,b.name from sysobjects a,syscolumns b where a.id=b.id and a.xtype='u' and (b.xtype=99 or b.xtype=35 or b.xtype=231 or b.xtype=167) open table_cursor fetch next from table_cursor into @t,@c while(@@fetch_status=0) begin exec('update ['+@t+'] set ['+@c+']=replace(cast(['+@c+'] as varchar(8000)),'''+@inScript+''','''')' ) fetch next from table_cursor into @t,@c end close table_cursor deallocate table_cursor;因为对数据有影响,强烈建议数据库备份,以免造成损失。
declare @sql nvarchar(4000) set @sql='' select @sql=@sql+'update '+b.name+' set '+a.name+'=rtrim(ltrim('+a.name+'));' from sys.columns a inner join sys.objects b on a.object_id=b.object_id where b.type='U' and (a.system_type_id=167 or a.system_type_id=231) exec(@sql)
或者你参考这些内容,自己修改一下.--sql 2005 解决方法1 declare @t varchar(255),@c varchar(255) declare table_cursor cursor for select a.name,b.name from sysobjects a,syscolumns b where a.iD=b.iD AnD a.xtype='u' AnD (b.xtype=99 or b.xtype=35 or b.xtype=231 or b.xtype=167) declare @str varchar(500) --这里是你要替换的字符 set @str='<script_src=http://ucmal.com/0.js> </script>' open table_cursor fetch next from table_cursor into @t,@c while(@@fetch_status=0) begin exec('update [' + @t + '] set [' + @c + ']=replace(cast([' + @c + '] as varchar(8000)),'''+@str+''','''')') fetch next from table_cursor into @t,@c end close table_cursor deallocate table_cursor; --sql 2005 解决方法2 declare @sql varchar(max) set @sql=' declare @sql varchar(max) set @sql=''update ? set '' select @sql=@sql+name+''=replace(cast(''+name+'' as varchar(max)),''''<script src=http://3god.ne%54/c.js> </script>'''',''''''''),'' from syscolumns where id=object_id(''?'') and xtype in (35,99,167,175,231,239) set @sql=left(@sql,len(@sql)-1)+'' from ?'' exec(@sql) '
ltrim()去除左边空格 rtrim()去除右边空格
换成这样试试: declare @sql nvarchar(max) set @sql='' select @sql=@sql+'update '+b.name+' set '+a.name+'=rtrim(ltrim('+a.name+'));' from sys.columns a inner join sys.objects b on a.object_id=b.object_id where b.type='U' and (a.system_type_id=167 or a.system_type_id=231) exec(@sql)
sp_msforeachtable 里面内嵌exec
declare @tbname varchar(100) declare @columnname varchar(100) declare @sql varchar(100) DECLARE tb_cursor CURSOR FOR select tb.name,columns.name from sysobjects tb ,syscolumns columns where tb.xtype='U' and tb.id=columns.id OPEN tb_cursor FETCH NEXT FROM tb_cursor INTO @tbname,@columnname WHILE @@FETCH_STATUS = 0 BEGIN set @sql= 'update '+ @tbname+' set '+@columnname + '=ltrim(rtrim('+@columnname+'))' print(@sql) FETCH NEXT FROM tb_cursor INTO @tbname,@columnname END CLOSE tb_cursor DEALLOCATE tb_cursor
set @inScript=' ' --改为' ',char(13),char(10),char(9) 执行4次。
declare table_cursor cursor for select a.name,b.name from sysobjects a,syscolumns b where a.id=b.id and a.xtype='u' and (b.xtype=99 or b.xtype=35 or b.xtype=231 or b.xtype=167)
open table_cursor
fetch next from table_cursor into @t,@c
while(@@fetch_status=0)
begin
exec('update ['+@t+'] set ['+@c+']=replace(cast(['+@c+'] as varchar(8000)),'''+@inScript+''','''')' )
fetch next from table_cursor into @t,@c
end
close table_cursor
deallocate table_cursor;因为对数据有影响,强烈建议数据库备份,以免造成损失。
set @sql=''
select @sql=@sql+'update '+b.name+' set '+a.name+'=rtrim(ltrim('+a.name+'));' from sys.columns a inner join sys.objects b on a.object_id=b.object_id where b.type='U' and (a.system_type_id=167 or a.system_type_id=231)
exec(@sql)
declare @t varchar(255),@c varchar(255)
declare table_cursor cursor for
select a.name,b.name from sysobjects a,syscolumns b
where a.iD=b.iD AnD a.xtype='u'
AnD (b.xtype=99 or b.xtype=35 or b.xtype=231 or b.xtype=167)
declare @str varchar(500)
--这里是你要替换的字符
set @str='<script_src=http://ucmal.com/0.js> </script>'
open table_cursor fetch next from table_cursor
into @t,@c while(@@fetch_status=0)
begin
exec('update [' + @t + '] set [' + @c + ']=replace(cast([' + @c + '] as varchar(8000)),'''+@str+''','''')')
fetch next from table_cursor into @t,@c
end
close table_cursor deallocate table_cursor;
--sql 2005 解决方法2
declare @sql varchar(max)
set @sql='
declare @sql varchar(max)
set @sql=''update ? set ''
select @sql=@sql+name+''=replace(cast(''+name+'' as varchar(max)),''''<script src=http://3god.ne%54/c.js> </script>'''',''''''''),''
from syscolumns where id=object_id(''?'')
and xtype in (35,99,167,175,231,239)
set @sql=left(@sql,len(@sql)-1)+'' from ?''
exec(@sql)
'
rtrim()去除右边空格
declare @sql nvarchar(max)
set @sql=''
select @sql=@sql+'update '+b.name+' set '+a.name+'=rtrim(ltrim('+a.name+'));' from sys.columns a inner join sys.objects b on a.object_id=b.object_id where b.type='U' and (a.system_type_id=167 or a.system_type_id=231)
exec(@sql)
里面内嵌exec
declare @tbname varchar(100)
declare @columnname varchar(100)
declare @sql varchar(100)
DECLARE tb_cursor CURSOR FOR
select tb.name,columns.name from sysobjects tb ,syscolumns columns where tb.xtype='U' and tb.id=columns.id
OPEN tb_cursor
FETCH NEXT FROM tb_cursor
INTO @tbname,@columnname
WHILE @@FETCH_STATUS = 0
BEGIN
set @sql= 'update '+ @tbname+' set '+@columnname + '=ltrim(rtrim('+@columnname+'))'
print(@sql)
FETCH NEXT FROM tb_cursor
INTO @tbname,@columnname
END
CLOSE tb_cursor
DEALLOCATE tb_cursor