替换数据库所有表内容

/*
描述：    字符串分割
作者：    Rich.Tang
时间：    2012-07-17
参数：    @s        待分拆的字符串,如'1,11,21,31'
        @split    分隔符,如','
*/
Create function dbo.f_splitStr(
    @s varchar(8000),
    @split varchar(10)
)returns @re table(id int,col varchar(8000))
as 
begin
    declare @i int
    set @i=1
    while charindex(@split,@s)>0
    begin
        insert @re values(@i,left(@s,charindex(@split,@s)-1))
        set @s=stuff(@s,1,charindex(@split,@s),'')
        set @i=@i+1
    end
    if len(@s)<>0
        insert @re values(@i,@s)
    return
end

go

/*
描述：    单表字段内容替换
作者：    Rich.Tang
时间：    2012-07-17
参数：    @tableName    表名
        @colList    字段集合以逗号分隔，如如'A,B'
        @oldValue    原值
        @newValue    新值
*/
alter proc SP_ColumnValue
    @tableName nvarchar(100),
    @colList nvarchar(2000),
    @oldValue nvarchar(1000),
    @newValue nvarchar(1000)
as
begin
    if LEN(@colList)=0 return
    
    declare @colSql nvarchar(max)
    declare @colName nvarchar(100)
    
    declare @count int,@i int
    select @count=COUNT(*) from dbo.f_splitStr(@colList,',')
    set @i=1

    set @colSql=''
    while @i<=@count
    begin
        set @colName=''
        select @colName=col from dbo.f_splitStr(@colList,',') where id=@i
    
        declare @replaceColName nvarchar(500)
        set @replaceColName='[' + @colName + ']'
        --字段类型为text或ntext
        if exists(SELECT * FROM SysColumns WHERE name=@colName and xtype in(35,99))
            set @replaceColName = 'cast([' + @colName + '] as nvarchar(max))'
        
        set @colSql = @colSql + '[' + @colName + ']=REPLACE(' + @replaceColName + ',''' + @oldValue + ''',''' + @newValue + '''),'
        
        set @i=@i+1
    end
    
    if len(@colSql)<>0 set @colSql=SUBSTRING(@colSql,1,len(@colSql)-1)

    declare @sql nvarchar(max)
    set @sql='update '+@tableName+' set ' + @colSql
    print @sql
    print '-------------------------------------------------------------------------------------'
    
    exec(@sql)
end

go 

/*
批量替换DB_58ShuShi数据库的数据
*/
declare @oldValue nvarchar(1000),@newValue nvarchar(1000)
set @oldValue='a'
set @newValue = 'b'

--游标遍历当前数据库所有表
declare @tableName nvarchar(100)
declare abc cursor         
for SELECT NAME FROM DB_58ShuShi..SysObjects Where XType='U' AND category=0 ORDER BY Name
open abc         
fetch next from abc into @tableName        
while @@fetch_status = 0 
begin
    declare @colList nvarchar(max)
    set @colList=''
    --找出类型为text,ntext,varchar,nvarchar的字段
    SELECT @colList=@colList+name+',' FROM SysColumns WHERE id=Object_Id(@tableName) and xtype in(35,99,167,231)
    if len(@colList)<>0    set @colList = SUBSTRING(@colList,1,LEN(@colList)-1)

    --调用SP_ColumnValue存储过程进行内容替换
    exec SP_ColumnValue @tableName,@colList,@oldValue,@newValue
    
    fetch next from abc into @tableName        
end
close abc         
deallocate abc