SQL server 通过存储过程实现全库数据修改（全库搜索需要修改的数据）

秒速五厘米 2022-05-20 01:52 243阅读 0赞

修改一批数据、无法断定存在于哪个表中、  
大体情况如下：  
![这里写图片描述][70]

odata列为旧数据、需要修改的数据  
ndata列为新数据、更新后的数据  
需要将全库所有内容为odata列的数据更新为ndata列  
无法断定具体哪张表存在相同数据、

--------------------

## 创建存储过程查找需要修改的字段 ##

creat PROCEDURE [dbo].[proc_uptmp_ywsj]
    
    AS
        BEGIN SET NOCOUNT ON;
    
        --变量定义
        --计算过程执行时长
            DECLARE @starttime DATETIME;
            DECLARE @totaltime INT;
        --流程控制(成功失败)
            DECLARE @Result INT;
        --执行影响行数
            DECLARE @ROW INT;
        --错误日志
            DECLARE @ErrorMessage VARCHAR(1000);
    
        --变量赋值
            SET @starttime = GETDATE();
            SET @Result = 0;
    
        --创建临时表
            CREATE TABLE #tmp_pdchg_z ( table_name VARCHAR(100) , filed_name VARCHAR(100) );
    
         --全库筛选可能存在需修改数据的列、数据类型等条件根据实际情况选择
            INSERT INTO #tmp_pdchg_z ( table_name , filed_name ) SELECT a.table_name , a.filed_name FROM ( SELECT d.name table_name , a.colorder 字段序号 , a.name filed_name , ( CASE WHEN COLUMNPROPERTY(a.id, a.name, 'IsIdentity') = 1 THEN '√' ELSE '' END ) 标识 , ( CASE WHEN ( SELECT COUNT(*) FROM sysobjects WHERE ( name IN ( SELECT name FROM sysindexes WHERE ( id = a.id ) AND ( indid IN ( SELECT indid FROM sysindexkeys WHERE ( id = a.id ) AND ( colid IN ( SELECT colid FROM syscolumns WHERE ( id = a.id ) AND ( name = a.name ) ) ) ) ) ) ) AND ( xtype = 'PK' ) ) > 0 THEN '√' ELSE '' END ) 主键 , b.name 类型 , a.length 占用字节数 , COLUMNPROPERTY(a.id, a.name, 'PRECISION') AS 长度 , ISNULL(COLUMNPROPERTY(a.id, a.name, 'Scale'), 0) AS 小数位数 , ( CASE WHEN a.isnullable = 1 THEN '√' ELSE '' END ) 允许空 , ISNULL(e.text, '') 默认值 , ISNULL(g.[value], ' ') AS [说明] FROM syscolumns a LEFT JOIN systypes b ON a.xtype = b.xusertype INNER JOIN sysobjects d ON a.id = d.id AND d.xtype = 'U' AND d.name <> 'dtproperties' LEFT JOIN syscomments e ON a.cdefault = e.id LEFT JOIN sys.extended_properties g ON a.id = g.major_id AND a.colid = g.minor_id LEFT JOIN sys.extended_properties f ON d.id = f.class AND f.minor_id = 0 WHERE b.name IS NOT NULL AND d.name IN ( SELECT name FROM sysobjects WHERE xtype = 'U' AND name NOT LIKE '%API%' AND name NOT LIKE '%Workflow%' AND name NOT LIKE '%Membership%' AND name NOT LIKE '%LOG%' ) ) a WHERE a.主键 ='' AND a.默认值 ='' AND a.类型 ='varchar' OR a.类型 = 'nvarchar';
                --SELECT *
                --FROM #tmp_pdchg_z
    
            DECLARE @tb_f_yj VARCHAR(500);
            DECLARE @table_name VARCHAR(50);
            DECLARE @filed_name VARCHAR(50);
            DECLARE @T TABLE ( cc VARCHAR(20) );
         --开启事务
         -- BEGIN TRAN;
         -- BEGIN TRY
         -----游标循环判断可能存在需修改数据的列是否存在需修改数据
                DECLARE tb_f_cursor CURSOR
                FOR
                    SELECT 'SELECT top 1 ' + filed_name + ' FROM ' + table_name + ' a WHERE EXISTS (SELECT 1 FROM tmp_ywsj WHERE odata=a.' + filed_name + ' );' , table_name , filed_name FROM #tmp_pdchg_z;
                OPEN tb_f_cursor;
                FETCH NEXT   FROM tb_f_cursor INTO @tb_f_yj, @table_name,
                    @filed_name;
                WHILE @@fetch_status = 0
         --计算逻辑
                    BEGIN INSERT INTO @T EXEC ( @tb_f_yj );
    
                        IF EXISTS ( SELECT 1 FROM @T ) ---将实际存在需修改数据的表名、列名插入表中 BEGIN INSERT INTO uptmp_ywsj VALUES ( @table_name, @filed_name );
                            END;
    
                        DELETE FROM @T;
    
                        FETCH NEXT FROM tb_f_cursor INTO @tb_f_yj, @table_name,@filed_name;
                    END;    
                CLOSE tb_f_cursor;
                DEALLOCATE tb_f_cursor;
    
    
         --计算总时长
                SET @totaltime = DATEDIFF(SECOND, @starttime, GETDATE());
    
            --END TRY
            --BEGIN CATCH
    
            -- IF @@TRANCOUNT > 0
            -- BEGIN
            -- ROLLBACK TRAN;
            -- SELECT @ErrorMessage = '操作发生异常:'
            -- + CAST(ERROR_NUMBER() AS VARCHAR) + ','
            -- + ERROR_MESSAGE() ,
            -- @Result = -1;
            -- END;
    
            --END CATCH;
    
            --IF @@TRANCOUNT > 0
            -- AND @Result = 0
            -- BEGIN
            -- COMMIT TRAN;
            -- END;
    
            --IF @@TRANCOUNT > 0
            -- AND @Result <> 0
            -- BEGIN
            -- ROLLBACK TRAN;
            -- END;
    
        --SELECT @b AS b;
    
        --删除临时表
            IF EXISTS ( SELECT * FROM tempdb..sysobjects WHERE id = OBJECT_ID('tempdb..#tmp_aa') ) BEGIN DROP TABLE #tmp_aa;
                END;
    
        --记录日志
       --INSERT INTO tb_SYSLOGS(Logger,Logtime,LogIP,Logname,LogTotalTime,LevelCode,Message,LogSQL)
       -- SELECT 'Templete' ,
       -- GETDATE() ,
       -- '::1' ,
       -- 'sa' ,
       -- @totaltime ,
       -- 'proc_uptmp_pdchg' ,
       -- '' ,
       -- '影响行数' + CAST(@ROW AS VARCHAR(10)) + ',' + @ErrorMessage;
    
        END;

--------------------

备注: 其中  
a.主键 =”  
AND a.默认值 =”  
AND a.类型 =’varchar’  
OR a.类型 = ‘nvarchar’  
根据实际情况筛选

--------------------

## 创建此过程中需要用到的表 ##

------存放需修改数据的表
    CREATE TABLE [dbo].[tmp_ywsj]( odata [varchar](100) NULL, ndata [varchar](100) NULL ) ON [PRIMARY] ;
    
    -----创建表用于存放数据库中实际需修改的列
    CREATE TABLE uptmp_ywsj (table_name VARCHAR(100), filed_name VARCHAR(100));

--------------------

## 执行存储过程 ##

EXEC proc_uptmp_ywsj

实际需修改的列会写入uptmp\_ywsj表中

--------------------

## 根据uptmp\_ywsj 生成update脚本 ##

SELECT 
    'CREATE NONCLUSTERED INDEX [NON-'+table_name+'_'+filed_name+'] ON '+table_name+' ('+filed_name+');',
    
    'UPDATE SET A.'+filed_name+' = B.ndata, a.UpdatedAt = GETDATE() FROM dbo.'+table_name+' A, dbo.tmp_ywsj B WHERE B.odata = a.'+filed_name+';' ,
    
    'DROP INDEX [NON-'+table_name+'_'+filed_name+'] ON dbo.'+table_name+';'
    
     FROM uptmp_ywsj

执行查询结果

[70]: /images/20220520/19119f5efe534abd82aefd5da7af04a3.png