如何遍历数据库含ModifiedBy列的表批量更新该字段内容

操作前必读：请务必提前完成全库完整备份，优先在测试环境验证脚本逻辑，确认无误后再操作生产环境，避免数据丢失。

适用场景 #

针对SQL Server数据库，批量更新所有包含ModifiedBy列的表，对该列存储的邮箱地址做加盐哈希脱敏。

实现方法 #

方法1：手动生成脚本逐表校验后执行 #

执行以下查询即可自动生成所有符合条件表的更新语句，你可以逐条核对表名和逻辑无误后，复制生成的语句批量执行：

SELECT 
    'UPDATE [' + t.name + '] SET [ModifiedBy] = CONVERT(nvarchar(max), HASHBYTES(''SHA'', CONCAT(''MY_SALT_VALUE'', [ModifiedBy])), 2);' AS 待执行更新语句
FROM 
    sys.tables t
INNER JOIN 
    sys.columns c ON t.object_id = c.object_id
WHERE 
    c.name = 'ModifiedBy'
-- 如需排除特定表可新增下方过滤条件
-- AND t.name NOT IN ('要排除的表名1', '要排除的表名2')

方法2：自动批量执行（已验证逻辑后使用） #

如果已经在测试环境确认逻辑没问题，可以直接用动态SQL自动拼接并执行所有更新操作：

DECLARE @UpdateScript NVARCHAR(MAX) = N''

-- 拼接所有表的更新语句
SELECT 
    @UpdateScript = @UpdateScript + N'UPDATE [' + t.name + N'] SET [ModifiedBy] = CONVERT(nvarchar(max), HASHBYTES(''SHA'', CONCAT(''MY_SALT_VALUE'', [ModifiedBy])), 2);' + NCHAR(13)
FROM 
    sys.tables t
INNER JOIN 
    sys.columns c ON t.object_id = c.object_id
WHERE 
    c.name = 'ModifiedBy'

-- 打印生成的完整脚本用于校验
PRINT @UpdateScript
-- 确认脚本无误后，取消注释下方语句即可执行更新
-- EXEC sp_executesql @UpdateScript

补充说明 #

• 请将脚本中的MY_SALT_VALUE替换为你自定义的随机盐值，妥善保管盐值避免泄露，防止原始邮箱被反推。
• 若ModifiedBy列存在NULL值，现有逻辑不会修改NULL值，如需统一处理可新增ISNULL函数调整SET逻辑。
• 如需更高安全强度的哈希算法，可将语句中的SHA替换为SHA2_256或SHA2_512。

内容的提问来源于stack exchange，提问作者RAM