SQL Server遍历所有表删除JSON数组中指定Branch值的元素

核心优化点 #

• 自动从系统表匹配所有包含BranchList列的用户表，无需手动逐个指定表名
• 采用集合操作替代原有逐行循环方案，执行效率提升明显
• 全程做转义处理，避免SQL注入风险和特殊表名导致的语法错误

通用解决方案代码（支持SQL Server 2017及以上版本） #

DECLARE @SQL NVARCHAR(MAX) = N''

-- 自动生成所有包含BranchList列的表的更新语句
SELECT @SQL += N'
UPDATE ' + QUOTENAME(s.name) + N'.' + QUOTENAME(t.name) + N'
SET BranchList = (
    SELECT value
    FROM OPENJSON(BranchList)
    WHERE JSON_VALUE(value, ''$.Branch'') != 253
    FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
)
WHERE EXISTS (
    SELECT 1 
    FROM OPENJSON(BranchList)
    WHERE JSON_VALUE(value, ''$.Branch'') = 253
);'
FROM sys.tables t
JOIN sys.columns c ON t.object_id = c.object_id
JOIN sys.schemas s ON t.schema_id = s.schema_id
WHERE c.name = 'BranchList' AND t.type = 'U'

-- 执行前先打印验证生成的SQL是否符合预期，确认无误后再打开EXEC执行
PRINT @SQL
-- EXEC sp_executesql @SQL

说明：上述写法无需显式声明JSON结构，会保留BranchList元素中除Branch外的所有原有字段，适配性更强。

低版本兼容方案（支持SQL Server 2016） #

如果使用的是不支持STRING_AGG的低版本SQL Server，可使用游标版本实现：

DECLARE @TableName NVARCHAR(256), @SQL NVARCHAR(MAX)
DECLARE table_cursor CURSOR FOR
SELECT QUOTENAME(s.name) + '.' + QUOTENAME(t.name)
FROM sys.tables t
JOIN sys.columns c ON t.object_id = c.object_id
JOIN sys.schemas s ON t.schema_id = s.schema_id
WHERE c.name = 'BranchList' AND t.type = 'U'

OPEN table_cursor
FETCH NEXT FROM table_cursor INTO @TableName

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @SQL = N'
    UPDATE ' + @TableName + N'
    SET BranchList = (
        SELECT value
        FROM OPENJSON(BranchList)
        WHERE JSON_VALUE(value, ''$.Branch'') != 253
        FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
    )
    WHERE EXISTS (
        SELECT 1 
        FROM OPENJSON(BranchList)
        WHERE JSON_VALUE(value, ''$.Branch'') = 253
    );'

    PRINT @SQL -- 先验证语句正确性再执行
    -- EXEC sp_executesql @SQL
    FETCH NEXT FROM table_cursor INTO @TableName
END

CLOSE table_cursor
DEALLOCATE table_cursor

注意事项 #

• 执行操作前务必先备份数据库，避免误操作导致数据丢失
• 如果表数据量较大，建议拆分分批执行，避免长时间锁表影响业务

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