SQL中全库批量替换 存储过程和调用方法

时间:2015/12/1 10:33:00来源:互联网 作者:flyso 点击: 902 次

-- =============================================

-- Author: FLYSO
-- Create date: 2014
-- Description: FLYSO.CN
-- =============================================
Create PROCEDURE [dbo].[ReplaceText]
@Str1 nvarchar(500),
@Str2 nvarchar(500)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
 
/**********以下为操作实体************/
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) 为char,varchar,nchar,nvarchar类型
        select name from syscolumns where xtype in (231,167,239,175) and id=@tbID
   open cur1
   fetch next from cur1 into @columnName
   while @@fetch_status=0
   begin
      set @sql='update [' + @tableName + '] set ['+ @columnName +']= replace(['+@columnName+'],'''+@Str1+''','''+@Str2+''') where ['+@columnName+'] like ''%'+@Str1+'%'''          
      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
 
END

/**********以下为调用方法************/

 exec ReplaceText 'xxxxxx','flyso.cn'

Copyright © 2005 - 2016 flyso.cn. 飞搜 版权所有 鄂ICP备11002783号-3