如何在Snowflake数据库中找出所有全为NULL值的列

我明白你面对70多张表、3000多个字段的困境，手动写查询肯定不现实，下面给你分享单表和全库范围的解决方案，完全不用逐个列写语句：

单表快速检查 #

如果只需要检查某一张表，我们可以用动态生成的SQL一次性验证所有列：

高效的检查逻辑 #

用MAX(列名) IS NULL来判断是最优的——因为MAX()会忽略NULL值，如果结果为NULL，说明该列所有行都是NULL（相比COUNT()，它在找到第一个非NULL值时就会停止遍历，性能更好）。

你可以运行下面的查询，替换MY_TABLE和对应的库、模式名，生成该表所有列的检查语句：

SET target_db = 'PROD_DB';
SET target_schema = 'PUBLIC';
SET target_table = 'MY_TABLE';

SELECT 'SELECT ''' || $target_db || ''' AS db, ''' || $target_schema || ''' AS schema, ''' || $target_table || ''' AS table, ''' || COLUMN_NAME || ''' AS all_null_column FROM ' 
       || QUOTE_IDENT($target_db) || '.' || QUOTE_IDENT($target_schema) || '.' || QUOTE_IDENT($target_table) 
       || ' HAVING MAX(' || QUOTE_IDENT(COLUMN_NAME) || ') IS NULL;'
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_CATALOG = $target_db
  AND TABLE_SCHEMA = $target_schema
  AND TABLE_NAME = $target_table;

把生成的所有SQL语句复制执行，返回的结果就是该表中全为NULL的列。

或者直接生成一个UNION ALL的查询，一次性得到结果：

SELECT STRING_AGG(
  'SELECT ''' || $target_db || ''', ''' || $target_schema || ''', ''' || $target_table || ''', ''' || COLUMN_NAME || ''' FROM ' 
  || QUOTE_IDENT($target_db) || '.' || QUOTE_IDENT($target_schema) || '.' || QUOTE_IDENT($target_table) 
  || ' HAVING MAX(' || QUOTE_IDENT(COLUMN_NAME) || ') IS NULL',
  ' UNION ALL '
) AS single_table_check_query
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_CATALOG = $target_db
  AND TABLE_SCHEMA = $target_schema
  AND TABLE_NAME = $target_table;

执行生成的这个大查询，就能直接拿到该表所有全NULL列的明细。

全库批量检查 #

针对整个数据库，我们可以利用Snowflake的INFORMATION_SCHEMA.COLUMNS自动生成所有表的检查语句，完全自动化处理：

步骤1：生成全库检查的动态SQL #

运行下面的查询，它会为你的PROD_DB中所有表和列生成检查语句，并拼接成一个完整的UNION ALL查询：

SELECT STRING_AGG(
  'SELECT ''' || TABLE_CATALOG || ''' AS database_name, ''' || TABLE_SCHEMA || ''' AS schema_name, ''' || TABLE_NAME || ''' AS table_name, ''' || COLUMN_NAME || ''' AS column_name FROM ' 
  || QUOTE_IDENT(TABLE_CATALOG) || '.' || QUOTE_IDENT(TABLE_SCHEMA) || '.' || QUOTE_IDENT(TABLE_NAME) 
  || ' HAVING MAX(' || QUOTE_IDENT(COLUMN_NAME) || ') IS NULL',
  ' UNION ALL '
) AS full_database_check_query
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_CATALOG = 'PROD_DB'; -- 替换为你的数据库名

这个查询会输出一个很长的SQL字符串，里面包含了对每一列的检查逻辑。

步骤2：执行生成的SQL #

把步骤1得到的结果复制出来，作为新的查询执行，就能得到整个数据库中所有全为NULL的列，结果会清晰展示数据库名、模式名、表名和对应的列名。

注意事项 #

• 对于超大型表，这个查询可能会耗时较长，建议在业务低峰期执行，或者按模式分批次处理（比如在WHERE子句中添加TABLE_SCHEMA = 'XXX'）。
• 对于变体（VARIANT）、数组（ARRAY）等复杂数据类型，MAX()可能需要特殊处理，但常规数据类型（字符串、数字、日期等）都能正常工作。

进阶：用存储过程自动化 #

如果你需要定期做这个检查，可以创建一个存储过程，一键执行全库检查：

CREATE OR REPLACE PROCEDURE FIND_ALL_NULL_COLUMNS(target_db VARCHAR)
RETURNS TABLE(database_name VARCHAR, schema_name VARCHAR, table_name VARCHAR, column_name VARCHAR)
LANGUAGE SQL
AS
$$
DECLARE
  column_cursor CURSOR FOR 
    SELECT TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME 
    FROM INFORMATION_SCHEMA.COLUMNS 
    WHERE TABLE_CATALOG = target_db;
  current_col RECORD;
  check_query VARCHAR;
  result_set TABLE(database_name VARCHAR, schema_name VARCHAR, table_name VARCHAR, column_name VARCHAR);
  temp_result TABLE(database_name VARCHAR, schema_name VARCHAR, table_name VARCHAR, column_name VARCHAR);
BEGIN
  FOR current_col IN column_cursor DO
    -- 生成当前列的检查语句
    check_query := 'SELECT ''' || current_col.TABLE_CATALOG || ''', ''' || current_col.TABLE_SCHEMA || ''', ''' || current_col.TABLE_NAME || ''', ''' || current_col.COLUMN_NAME || ''' FROM ' 
                   || QUOTE_IDENT(current_col.TABLE_CATALOG) || '.' || QUOTE_IDENT(current_col.TABLE_SCHEMA) || '.' || QUOTE_IDENT(current_col.TABLE_NAME) 
                   || ' HAVING MAX(' || QUOTE_IDENT(current_col.COLUMN_NAME) || ') IS NULL';
    
    -- 执行检查并收集结果
    temp_result := EXECUTE IMMEDIATE check_query;
    IF (SELECT COUNT(*) FROM temp_result) > 0 THEN
      result_set := result_set UNION ALL temp_result;
    END IF;
  END FOR;
  
  RETURN TABLE(result_set);
END;
$$;

调用存储过程的方式很简单：

CALL FIND_ALL_NULL_COLUMNS('PROD_DB');

它会自动遍历所有表和列，返回所有全为NULL的列信息。

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