mssql全库查找且替换字段

cultureSun

发布于 2023-11-24 09:25:11

1280

发布于 2023-11-24 09:25:11

文章被收录于专栏：cultureSun学安全cultureSun学安全

业务场景

两个业务实体合并。假设业务实体为人员A和人员B，需要将两个人员的相关数据合并，且保留人员B，删掉人员A。

通过分析实际的场景，在库中存的相关业务数据都是通过人员ID关联人员的，而人员ID在库中是唯一的值。所以只需要全库所有表中查找人员A的 ID，替换成人员B，即可完成两个人员的合并需求(注意排除存储人员的数据表)。

具体实现

分两种：

一种是某表某字段只存储一位人员ID
一种是此字段存储多位人员ID(中间有分隔符)

第一种：

DECLARE @OldValue NVARCHAR(MAX) = '人员A-ID'
DECLARE @NewValue NVARCHAR(MAX) = '人员B-ID'

DECLARE @TableName NVARCHAR(MAX)
DECLARE @ColumnName NVARCHAR(MAX)
DECLARE @UpdateStmt NVARCHAR(MAX)
DECLARE @SqlQuery NVARCHAR(MAX)
DECLARE @aExists INT
DECLARE @SQLString NVARCHAR(1000)

--创建游标，获取库中所有(表名,列名)
DECLARE tableCursor CURSOR FOR
SELECT t.name AS TableName, c.name AS ColumnName
FROM sys.tables t
INNER JOIN sys.columns c ON t.object_id = c.object_id
WHERE c.system_type_id IN (167, 175, 231, 239) -- 数据类型为char、varchar、nchar、nvarchar的列

--  CREATE TABLE #TempTable (
--     ID NVARCHAR(MAX),
--      CNAME NVARCHAR(MAX)
-- )
-- 
--获取游标中的行
OPEN tableCursor
FETCH NEXT FROM tableCursor INTO @TableName, @ColumnName

--循环游标
WHILE @@FETCH_STATUS = 0
BEGIN
        --用来排除某些表或者列
        IF @TableName='TABLE-A' OR @ColumnName='COLUMN-A'
        BEGIN
            FETCH NEXT FROM tableCursor INTO @TableName, @ColumnName
            CONTINUE
        END
        
        --判断此表此列是否存在 值 等于 人员A-ID ，如果有替换值
        SET @SqlQuery = 'SELECT @aExists=1 FROM ' + QUOTENAME(@TableName) + ' WHERE ' + QUOTENAME(@ColumnName) + ' = @OldValue'
        EXEC sp_executesql @SqlQuery, N'@OldValue NVARCHAR(MAX), @aExists INT OUTPUT', @OldValue, @aExists OUTPUT

        IF @aExists=1
        BEGIN
            --将相关的合并数据进行备份，备份表(bak_tran),此表结构应根据具体情况设计，以下只供参考。原本打算存在临时表（TempTable）
             SET @SQLString = 'SELECT ID,'''+@TableName+''','''+@ColumnName+''','''+@NewValue+''','''+@OldValue+''' FROM '+QUOTENAME(@TableName)+' WHERE ' +QUOTENAME(@ColumnName)+' = @OldValue'
            INSERT INTO [bak_tran](id,tname,cname,new,old) EXEC sp_executesql @SQLString,N'@OldValue NVARCHAR(MAX)',@OldValue
            
            --将 人员A-ID 替换为 人以员B-ID
      SET @UpdateStmt = 'UPDATE ' + QUOTENAME(@TableName) + ' SET ' + QUOTENAME(@ColumnName) + ' = CASE WHEN '+QUOTENAME(@ColumnName)+' =@OldValue THEN @NewValue ELSE '+QUOTENAME(@ColumnName)+' END'        
      EXEC sp_executesql @UpdateStmt, N'@OldValue NVARCHAR(MAX), @NewValue NVARCHAR(MAX)', @OldValue, @NewValue
        END
        
        SET @aExists = 0
    FETCH NEXT FROM tableCursor INTO @TableName, @ColumnName
END
-- SELECT * FROM #TempTable
--关闭且删除游标
CLOSE tableCursor
DEALLOCATE tableCursor

第二种：

DECLARE @OldValue NVARCHAR(MAX) = '人员A-ID'
DECLARE @NewValue NVARCHAR(MAX) = '人员B-ID'

DECLARE @TableName NVARCHAR(MAX)
DECLARE @ColumnName NVARCHAR(MAX)
DECLARE @UpdateStmt NVARCHAR(MAX)
DECLARE @SqlQuery NVARCHAR(MAX)
DECLARE @aExists INT
DECLARE @SQLString NVARCHAR(1000)

--创建游标，获取库中所有(表名,列名)
DECLARE tableCursor CURSOR FOR
SELECT t.name AS TableName, c.name AS ColumnName
FROM sys.tables t
INNER JOIN sys.columns c ON t.object_id = c.object_id
WHERE c.system_type_id IN (167, 175, 231, 239) -- 数据类型为char、varchar、nchar、nvarchar的列

--  CREATE TABLE #TempTable (
--     ID NVARCHAR(MAX),
--      CNAME NVARCHAR(MAX)
-- )
-- 
--获取游标中的行
OPEN tableCursor
FETCH NEXT FROM tableCursor INTO @TableName, @ColumnName

--循环游标
WHILE @@FETCH_STATUS = 0
BEGIN
        --用来排除某些表或者列
        IF @TableName='TABLE-A' OR @ColumnName='COLUMN-A'
        BEGIN
            FETCH NEXT FROM tableCursor INTO @TableName, @ColumnName
            CONTINUE
        END
        
        --判断此表此列是否存在 值 等于 人员A-ID ，如果有替换值
        SET @SqlQuery = 'SELECT @aExists=1 FROM ' + QUOTENAME(@TableName) + ' WHERE ' + QUOTENAME(@ColumnName) + ' = @OldValue'
        EXEC sp_executesql @SqlQuery, N'@OldValue NVARCHAR(MAX), @aExists INT OUTPUT', @OldValue, @aExists OUTPUT

        IF @aExists=1
        BEGIN
            --将相关的合并数据进行备份，备份表(bak_tran),此表结构应根据具体情况设计，以下只供参考。原本打算存在临时表（TempTable）
             SET @SQLString = 'SELECT ID,'''+@TableName+''','''+@ColumnName+''','''+@NewValue+''','''+@OldValue+''' FROM '+QUOTENAME(@TableName)+' WHERE ' +QUOTENAME(@ColumnName)+' = @OldValue'
            INSERT INTO [bak_tran](id,tname,cname,new,old) EXEC sp_executesql @SQLString,N'@OldValue NVARCHAR(MAX)',@OldValue
            
            --将 人员A-ID 替换为 人以员B-ID
      SET @UpdateStmt = 'UPDATE ' + QUOTENAME(@TableName) + ' SET ' + QUOTENAME(@ColumnName) + ' = replace( '+QUOTENAME(@ColumnName)+',@OldValue,@NewValue) '
      EXEC sp_executesql @UpdateStmt, N'@OldValue NVARCHAR(MAX), @NewValue NVARCHAR(MAX)', @OldValue, @NewValue
        END
        
        SET @aExists = 0
    FETCH NEXT FROM tableCursor INTO @TableName, @ColumnName
END
-- SELECT * FROM #TempTable
--关闭且删除游标
CLOSE tableCursor
DEALLOCATE tableCursor