MSSQL中如何批量过滤err_* schema下多表仅保留含非OK值的记录

方案说明 #

因为涉及的schema、表、字段都是动态匹配的，无法写固定SQL处理，需要通过MSSQL系统视图生成动态SQL执行，可覆盖单次查询校验、批量生成过滤结果表、批量清理原表无效记录三类需求。

步骤1：先验证匹配的对象是否正确 #

先执行以下查询，确认要处理的schema、表、A开头字段没有误匹配，避免后续操作出错：

SELECT 
    s.name AS schema_name,
    t.name AS table_name,
    c.name AS column_name
FROM sys.schemas s
JOIN sys.tables t ON s.schema_id = t.schema_id
JOIN sys.columns c ON t.object_id = c.object_id
WHERE s.name LIKE 'err\_%' ESCAPE '\' -- 转义下划线，精准匹配err_开头的schema
AND c.name LIKE 'A[0-9]%' -- 匹配A开头加数字的列，过滤掉其他非A1/An格式的列
ORDER BY s.name, t.name, CAST(SUBSTRING(c.name,2,LEN(c.name)) AS INT)

步骤2：单表临时查询方案 #

如果只需查某一张表的符合要求的记录，直接拼接WHERE条件即可：

SELECT * FROM err_xxx.你的表名
WHERE A1 <> 'OK' OR A2 <> 'OK' OR A3 <> 'OK' -- 按实际存在的A列拼接

注意：如果A列允许为NULL，需要把NULL值判定为不等于'OK'的话，把条件修改为 ISNULL(Ax, '') <> 'OK' 即可

步骤3：全量批量处理方案 #

如果要对所有符合规则的表统一生成过滤结果，用以下动态SQL脚本：

DECLARE @sql NVARCHAR(MAX) = N''

-- 遍历所有符合要求的表，拼接每个表的处理语句
SELECT @sql += N'
-- 处理表：' + QUOTENAME(s.name) + '.' + QUOTENAME(t.name) + N'
SELECT * INTO ' + QUOTENAME(s.name) + '.' + QUOTENAME(t.name + '_filtered') + N'
FROM ' + QUOTENAME(s.name) + '.' + QUOTENAME(t.name) + N'
WHERE ' + 
    STUFF((
        SELECT N' OR ' + QUOTENAME(c.name) + N' <> ''OK'''
        FROM sys.columns c
        WHERE c.object_id = t.object_id
        AND c.name LIKE 'A[0-9]%'
        ORDER BY CAST(SUBSTRING(c.name,2,LEN(c.name)) AS INT)
        FOR XML PATH(''), TYPE
    ).value('.','NVARCHAR(MAX)'), 1, 4, N'') + N';
'
FROM sys.schemas s
JOIN sys.tables t ON s.schema_id = t.schema_id
WHERE s.name LIKE 'err\_%' ESCAPE '\'

-- 先打印生成的SQL，校验逻辑完全正确后再执行
PRINT @sql
-- 校验无误后取消下面的注释执行脚本
-- EXEC sp_executesql @sql

以上脚本默认会在同schema下生成带_filtered后缀的新表，存储过滤后的结果。如果需要直接删除原表中所有A列全为'OK'的无效记录，把SELECT * INTO ...部分修改为DELETE FROM ' + QUOTENAME(s.name) + '.' + QUOTENAME(t.name) + ' WHERE NOT ( 拼接OR条件后加)即可。

注意事项 #

• 执行批量操作前务必先校验生成的SQL逻辑，避免误删数据
• 如果A列存在非字符串类型，拼接条件时需要先转成字符串：`CAST(' + QUOTENAME(c.name) + N' AS NVARCHAR(100)) <> ''OK'''
• 表数据量大的话建议分批执行，避免长时间锁表影响业务

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