如何用SAS动态检查库/表/列是否存在并更新result_flg字段

核心思路是利用数据库自带的元数据视图批量关联查询，避免逐行遍历（比如游标），批量操作能大幅提升执行效率，尤其是数据量较大时。

实现逻辑 #

对于表中的每一行：

1. 若columnA非空，检查它是否存在于指定library（对应数据库/schema）的table中
2. 若columnB非空，执行同样的检查
3. result_flg的值规则：
   • 所有非空列都存在则设为true
   • 只要有一个非空列不存在则设为false
   • 若两列都为空，保持result_flg原状态

具体SQL示例 #

以下以不同数据库为例，给出批量更新的语句：

MySQL / PostgreSQL

UPDATE column_check_list c
SET result_flg = (
    CASE
        -- 两列都非空：需同时存在才为true
        WHEN columnA IS NOT NULL AND columnA != '' 
             AND columnB IS NOT NULL AND columnB != '' THEN
            (EXISTS(
                SELECT 1 FROM information_schema.columns 
                WHERE table_schema = c.library 
                  AND table_name = c.table 
                  AND column_name = c.columnA
             )
             AND EXISTS(
                SELECT 1 FROM information_schema.columns 
                WHERE table_schema = c.library 
                  AND table_name = c.table 
                  AND column_name = c.columnB
             ))
        -- 仅columnA非空：检查该列是否存在
        WHEN columnA IS NOT NULL AND columnA != '' THEN
            EXISTS(
                SELECT 1 FROM information_schema.columns 
                WHERE table_schema = c.library 
                  AND table_name = c.table 
                  AND column_name = c.columnA
            )
        -- 仅columnB非空：检查该列是否存在
        WHEN columnB IS NOT NULL AND columnB != '' THEN
            EXISTS(
                SELECT 1 FROM information_schema.columns 
                WHERE table_schema = c.library 
                  AND table_name = c.table 
                  AND column_name = c.columnB
            )
        -- 两列都为空，保持原值
        ELSE result_flg
    END
);

SQL Server

UPDATE c
SET result_flg = 
    CASE
        WHEN columnA IS NOT NULL AND columnA != '' 
             AND columnB IS NOT NULL AND columnB != '' THEN
            (EXISTS(
                SELECT 1 
                FROM sys.columns col
                JOIN sys.tables tab ON col.object_id = tab.object_id
                JOIN sys.schemas sch ON tab.schema_id = sch.schema_id
                WHERE sch.name = c.library 
                  AND tab.name = c.table 
                  AND col.name = c.columnA
             )
             AND EXISTS(
                SELECT 1 
                FROM sys.columns col
                JOIN sys.tables tab ON col.object_id = tab.object_id
                JOIN sys.schemas sch ON tab.schema_id = sch.schema_id
                WHERE sch.name = c.library 
                  AND tab.name = c.table 
                  AND col.name = c.columnB
             ))
        WHEN columnA IS NOT NULL AND columnA != '' THEN
            EXISTS(
                SELECT 1 
                FROM sys.columns col
                JOIN sys.tables tab ON col.object_id = tab.object_id
                JOIN sys.schemas sch ON tab.schema_id = sch.schema_id
                WHERE sch.name = c.library 
                  AND tab.name = c.table 
                  AND col.name = c.columnA
            )
        WHEN columnB IS NOT NULL AND columnB != '' THEN
            EXISTS(
                SELECT 1 
                FROM sys.columns col
                JOIN sys.tables tab ON col.object_id = tab.object_id
                JOIN sys.schemas sch ON tab.schema_id = sch.schema_id
                WHERE sch.name = c.library 
                  AND tab.name = c.table 
                  AND col.name = c.columnB
            )
        ELSE result_flg
    END
FROM column_check_list c;

优化与注意事项 #

• 权限要求：执行语句的用户需要有访问系统元数据视图的权限（比如MySQL需SELECT权限在information_schema，SQL Server需VIEW DEFINITION权限）。
• 大小写敏感：部分数据库（如Linux下的MySQL）对表/列名区分大小写，需确保检查时的名称与实际元数据一致。
• 大表分批更新：如果表数据量极大，一次性更新可能导致锁表时间过长，可按library或table分组，分批次执行更新（比如MySQL用LIMIT，SQL Server用TOP）。
• 元数据缓存：如果需要重复执行检查，可先将元数据导出到临时表并建立索引，进一步提升查询效率。

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