mssql批量替换整个数据库
Posted by adminMay 16
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 | declare @Str1 varchar(8000) SET @Str1='参数1' declare @Str2 varchar(8000) SET @Str2='参数2' SET nocount ON declare @tableName nvarchar(100),@columnName nvarchar(100),@tbID int,@iRow int,@iResult int declare @sql nvarchar(500) SET @iResult=0 declare cur cursor FOR SELECT name,id FROM sysobjects WHERE xtype='U' open cur fetch next FROM cur INTO @tableName,@tbID while @@fetch_status=0 begin declare cur1 cursor FOR --xtype in (231,167,239,175,99) 为char,varchar,nchar,nvarchar,ntext类型 SELECT name FROM syscolumns WHERE xtype IN (231,167,239,175,99) AND id=@tbID open cur1 fetch next FROM cur1 INTO @columnName while @@fetch_status=0 begin SET @sql='update [' + @tableName + '] set ['+ @columnName +']= replace(cast(['+@columnName+'] as varchar(8000)),'''+@Str1+''','''+@Str2+''') where ['+@columnName+'] like ''%'+@Str1+'%''' --update tablename set fieldA=replace(cast(fieldA as varchar(8000)) ,'aa','bb')这样的语句。 exec sp_executesql @sql SET @iRow=@@rowcount SET @iResult=@iResult+@iRow IF @iRow>0 begin print '表：'+@tableName+',列:'+@columnName+'被更新'+convert(varchar(10),@iRow)+'条记录;' end fetch next FROM cur1 INTO @columnName end close cur1 deallocate cur1 fetch next FROM cur INTO @tableName,@tbID end print '数据库共有'+convert(varchar(10),@iResult)+'条记录被更新!!!' close cur deallocate cur SET nocount off |