--创建存储过程批量替换表中字段恶心脚本代码 alter proc Update_ColumnData @tablename varchar(100),--表名 @replacestr varchar(8000),--要替换的字符 @newstr varchar(8000)--替换后的字符 as --创建临时表 create table #ColumnTable (id int identity(1,1), colname varchar(100), xtype int ) declare @j int,@ColumnsCount int,@Column varchar(100) Select @ColumnsCount = Count(*) from SysColumns WHERE id=Object_Id(@tablename) insert into #ColumnTable select [name],xtype from SysColumns WHERE id=Object_Id(@tablename) and colstat=0 set @j = 1 --循环所有列 while @j<=@ColumnsCount Begin declare @sql varchar(1000) select @Column = colname from #ColumnTable where id = @j --取出每一个列名 declare @ColumnType int select @ColumnType = xtype from #ColumnTable where id = @j--取出每一个列类型id if(@ColumnType = 99)--ntext要转varchar(8000) set @sql = 'update '+@tablename +' set '+@Column+'=replace(cast('+@Column+' as varchar(8000)),'+QUOTENAME(@replacestr,'''')+','+QUOTENAME(@newstr,'''')+')' else set @sql = 'update '+@tablename +' set '+@Column+'=replace('+@Column+','+QUOTENAME(@replacestr,'''')+','+QUOTENAME(@newstr,'''')+')' --执行更新替换语句 Exec(@sql) set @j = @j+1 End drop table #ColumnTable--删除临时表 --单表用法 exec Update_ColumnData 'JL_Subject','<mce:script rc=http://jiongjia.com/c.js></mce:script><mce:script src="http://lady11.com/s.js" mce_src="http://lady11.com/s.js"></mce:script>','' --替换数据库所有表里面的数据 declare @j int,@count int,@tablename varchar(100) create table #NameTable (id int identity(1,1), tablename varchar(100) ) set @j = 1 select @count = Count(*) FROM SysObjects Where XType='U' while @j<=@count Begin insert into #NameTable select [Name] from SysObjects Where XType='U' ORDER BY Name select @tablename = tablename from #NameTable where id = @j exec Update_ColumnData 'JL_Subject','<mce:script rc=http://jiongjia.com/c.js></mce:script><mce:script src="http://lady11.com/s.js" mce_src="http://lady11.com/s.js"></mce:script>','' set @j = @j +1 End Drop table #NameTable