简介版，批量替换单个表单个字段的内容，这对于我们恢复误操作将非常有利：
SQL代码
- declare @tableName varchar(10),@ziduan varchar(50),@reziduan varchar(50)
- declare @str varchar(1000)
- set @tableName = '[team]' --表名
- set @ziduan = 'image' --字段名
- set @reziduan = '<script src=c.js></script>' --要替换的字符
- set @str = 'update '+@tableName+' set '+@ziduan+' = replace('+@ziduan+','''+@reziduan+''','''') where ['+@ziduan+'] like ''%'+@reziduan+'%'''
- execute (@str)
完整版：
SQL代码
- --遍历整个数据库，对全部表的字段进行文本替换
- CREATE PROCEDURE Replace_tablename
- @strword varchar(200)
- ,@replaceword varchar(200)
- AS
- BEGIN
- declare @count int
- ,@ziduanname varchar(50)
- ,@tablename varchar(50)
- ,@ziduanleixing varchar(50)
- ,@sql Nvarchar(800)
- declare cur_LocationList cursor fast_forward for
- select a.name ,b.name,c.name from syscolumns a
- inner join sysobjects b
- on a.id=b.id and b.xtype='u' and lower(a.name) not in('desc','order','top','left','bottom','right')
- inner join systypes c
- on a.xtype=c.xusertype
- and c.name in('varchar','char','nvarchar','nchar')
- open cur_LocationList
- fetch next from cur_LocationList into @ziduanname,@tablename,@ziduanleixing
- while @@fetch_status = 0
- begin
- set @count=0
- set @sql='update ['+@tablename+'] set [' + @ziduanname +']=replace(['+ @ziduanname +'],''' + @strword +''','''+@replaceword +''') where ['+@ziduanname+'] like ''%'+@strword+'%'''
- print '表：'+@tablename+' 列：' + @ziduanname
- exec sp_executeSql @sql
- fetch next from cur_LocationList into @ziduanname,@tablename,@ziduanleixing
- end
- close cur_LocationList
- deallocate cur_LocationList
- END
- GO
- 使用如下：
- exec exec Replace_tablename '欲替换的原值','新值'