SQL批量修改数据库内容

Create proc dbo.UpdateTextColumn
@Table varchar(100),
@Columns varchar(200),
@old varchar(100),
@new varchar(100)
as
    set nocount on
    declare @sql nvarchar(2000)
    declare @Column varchar(50)
    declare @cpos int,@npos int
    set @cpos=1;
    set @npos=1;
    set @npos=charindex(',',@Columns,@cpos);

    while(@npos>0)
    begin
        set @Column = substring(@Columns,@cpos,@npos-@cpos);
        set @cpos = @npos+1
        set @npos=charindex(',',@Columns,@cpos);
        
        set @sql = 'update '+@Table+' set '+@Column+'=replace(cast('+@Column+' as varchar(8000)),@old,@new) where Datalength('+@Column+')<=8000';
        EXECUTE sp_executesql @Sql,
                           N'@old varchar(100),@new varchar(100)',
                           @old,
                           @new
        declare @ptr binary(16) ,@offset int,@dellen int
        
        set @dellen = len(@old)
        
        set @offset = 1
        while @offset>=1
        begin
            set @offset = 0
            set @sql = 'select     top 1 @offset = charindex('''+@old+''' , '+@Column+'), @ptr = textptr('+@Column+') from '+@Table+' where Datalength('+@Column+')>8000 and '+@Column+' like ''%'+@old+'%''';
            EXEC sp_executesql @Sql,N'@offset int OUTPUT,@ptr binary(16) OUTPUT,@old varchar(100)',
                               @offset OUTPUT,@ptr OUTPUT,@old;

           if @offset > 0
            begin
                set @offset = @offset-1

                set @sql='updatetext '+@Table+'.'+@Column+' @ptr @offset @dellen @new';
                EXEC sp_executesql @Sql,N'@offset int ,@ptr binary(16),@dellen int,@new varchar(100)',@offset,@ptr,@dellen,@new;
            end 
        end
end
go

Create proc dbo.ReplaceKeyWord
@old nvarchar(100),
@new nvarchar(100)
as
declare @sql nvarchar(1000)
set @sql=N' 
declare   @s   nvarchar(4000),@tbname   sysname 
select   @s=N'''',@tbname=N''?'' 
select   @s=@s+N'',''+quotename(a.name)+N''=replace(''+quotename(a.name)+N'',N'''''+@old+''''',N'''''+@new+''''')'' 
from   syscolumns   a,systypes   b 
where   a.id=object_id(@tbname)    
and   a.xusertype=b.xusertype 
and   b.name   like   N''%char'' 
if   @@rowcount>0 
begin 
set   @s=stuff(@s,1,1,N'''') 
exec(N''update   ''+@tbname+''   set   ''+@s) 
end '
--print @sql
exec   sp_msforeachtable   @sql;

set @sql=N' 
declare   @s   nvarchar(4000),@tbname   sysname 
select   @s=N'''',@tbname=N''?'' 
select   @s=@s+quotename(a.name)+N'',''
from   syscolumns   a,systypes   b 
where   a.id=object_id(@tbname)    
and   a.xusertype=b.xusertype 
and   b.name   like   N''%text'' 
if   @@rowcount>0 
begin 
exec UpdateTextColumn @tbname,@s,'''+@old+''','''+@new+'''
end
' ;
exec   sp_msforeachtable @sql
go

随机号码
IF EXISTS(SELECT * FROM sysobjects WHERE name='proc_PhoneNum')
    DROP PROCEDURE proc_PhoneNum
GO
CREATE PROCEDURE proc_PhoneNum
    @randCardID varchar(19) OUTPUT,      --输出参数
    @firstNo varchar(4)='132'   --输入参数，有默认值
    AS
        DECLARE @r numeric(15,8)   --15位数，保留8位小数
        DECLARE @tempStr char(10)
 
        select @r=RAND((DATEPART(mm,GETDATE())*100000)+(DATEPART(ss,GETDATE())*1000)+DATEPART(ms,GETDATE()))
        SET @tempStr=@r
        SET @randCardID=@firstNo+SUBSTRING(@tempStr,3,4)+''+SUBSTRING(@tempStr,7,4)
GO
测试
DECLARE @phoneNum nvarchar(20)
EXECUTE proc_PhoneNum @phoneNum OUTPUT
print '产生的随机手机号为：'+@phoneNum
insert phones values(@phoneNum)

--循环替换所有客户的号码
create proc UpdateUserPhone
as

IF EXISTS(SELECT * FROM sysobjects WHERE name='UpdateUserPhone')
    DROP PROCEDURE UpdateUserPhone
GO
create proc UpdateUserPhone
as

declare @id int
declare @maxid int
declare @count int
set @id=1
select @maxid=max(PID) from T_User

begin
 while @id<=@maxid
begin
DECLARE @phoneNum nvarchar(20)

select @count=count(1) from T_User where PID=@id
if @count>0
begin
EXECUTE proc_PhoneNum @phoneNum OUTPUT
print '产生的随机手机号为：'+@phoneNum
update T_User set FMobilePhone=@phoneNum where PID=@id
update T_CarOrder set FMobile=@phoneNum where KUserID=@id
waitfor delay'00:00:00:02'
end
set @id=@id+1
end
end

 示例

Exec ReplaceKeyWord 'aaa','bbb'