新手求助：如何在SQL Server数据库中查找所有空列？（附Oracle代码）

Hey there! Let's break down how to convert your Oracle query to SQL Server, plus cover general approaches for finding all-null columns across databases.

SQL Server Implementation #

Since you're used to Oracle's dynamic cursor approach, here's the equivalent for SQL Server. We'll use system catalog views to get column names, then run dynamic SQL to check if each column has zero non-null values (meaning every row is NULL):

DECLARE @ColumnName NVARCHAR(128), 
        @TableName NVARCHAR(128) = 'A', 
        @SchemaName NVARCHAR(128) = 'HR';
DECLARE @SQL NVARCHAR(MAX), 
        @NonNullCount INT;

-- Cursor to iterate over all columns in the target table
DECLARE ColumnCursor CURSOR FOR
SELECT c.name
FROM sys.columns c
JOIN sys.tables t ON c.object_id = t.object_id
JOIN sys.schemas s ON t.schema_id = s.schema_id
WHERE t.name = @TableName 
  AND s.name = @SchemaName;

OPEN ColumnCursor;
FETCH NEXT FROM ColumnCursor INTO @ColumnName;

WHILE @@FETCH_STATUS = 0
BEGIN
    -- Build dynamic SQL to count non-null values in the column
    SET @SQL = N'SELECT @NonNullCount = COUNT(' + QUOTENAME(@ColumnName) + N') 
                 FROM ' + QUOTENAME(@SchemaName) + N'.' + QUOTENAME(@TableName);
    
    -- Execute dynamic SQL safely and get the count
    EXEC sp_executesql @SQL, 
                       N'@NonNullCount INT OUTPUT', 
                       @NonNullCount OUTPUT;
    
    -- If no non-null values exist, print the column name
    IF @NonNullCount = 0
    BEGIN
        PRINT 'Column ' + @ColumnName + ' contains only NULL values';
    END
    
    FETCH NEXT FROM ColumnCursor INTO @ColumnName;
END

CLOSE ColumnCursor;
DEALLOCATE ColumnCursor;

A few key notes:

• QUOTENAME() wraps column/schema/table names in brackets, handling cases where names have spaces or special characters.
• sp_executesql is safer than raw EXEC because it parameterizes the output variable, reducing SQL injection risk.
• COUNT(column_name) ignores NULL values, so a result of 0 means every row in that column is NULL.

General Approach for Finding All-Null Columns #

The core logic works across databases, but you'll need to adjust the system views used to fetch column metadata:

1. Oracle (your original approach, refined) #

Your existing code is on the right track—here's a cleaned-up version:

SET serveroutput ON;
DECLARE 
    v_count NUMBER;
    CURSOR c2 IS 
        SELECT column_name 
        FROM all_tab_columns 
        WHERE table_name = 'A' 
          AND owner = 'HR';
BEGIN
    FOR r1 IN c2 LOOP
        EXECUTE immediate 'SELECT COUNT('||r1.column_name||') FROM HR.A' 
        INTO v_count;
        IF v_count = 0 THEN
            dbms_output.put_line('Column ' || r1.column_name || ' has all NULL values');
        END IF;
    END LOOP;
END;
/

2. MySQL #

Use information_schema.columns to get column names, then dynamic SQL via a stored procedure:

SET @schema = 'HR', @table = 'A';

DELIMITER //
CREATE PROCEDURE FindAllNullColumns()
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE col_name VARCHAR(128);
    DECLARE cur CURSOR FOR 
        SELECT column_name 
        FROM information_schema.columns 
        WHERE table_schema = @schema 
          AND table_name = @table;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    OPEN cur;
    read_loop: LOOP
        FETCH cur INTO col_name;
        IF done THEN
            LEAVE read_loop;
        END IF;
        
        SET @sql = CONCAT('SELECT COUNT(', col_name, ') INTO @cnt FROM ', @schema, '.', @table);
        PREPARE stmt FROM @sql;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;
        
        IF @cnt = 0 THEN
            SELECT CONCAT('Column ', col_name, ' has all NULL values') AS result;
        END IF;
    END LOOP;
    CLOSE cur;
END //
DELIMITER ;

-- Call the procedure
CALL FindAllNullColumns();

3. PostgreSQL #

Use PL/pgSQL to create a function that returns all all-null columns:

CREATE OR REPLACE FUNCTION find_all_null_columns(schema_name text, table_name text)
RETURNS SETOF text AS $$
DECLARE
    rec record;
    cnt integer;
BEGIN
    FOR rec IN 
        SELECT column_name 
        FROM information_schema.columns 
        WHERE table_schema = schema_name 
          AND table_name = table_name
    LOOP
        EXECUTE format('SELECT COUNT(%I) FROM %I.%I', rec.column_name, schema_name, table_name) 
        INTO cnt;
        IF cnt = 0 THEN
            RETURN NEXT rec.column_name;
        END IF;
    END LOOP;
    RETURN;
END;
$$ LANGUAGE plpgsql;

-- Get the results
SELECT find_all_null_columns('HR', 'A');

Core Universal Steps #

No matter which database you're using, the process boils down to:

• Fetch column metadata: Query the database's system catalog or information_schema to get all columns for your target table.
• Dynamic count check: For each column, run a COUNT(column_name) query (which ignores NULLs).
• Flag all-null columns: If the count returns 0, the column contains only NULL values—log or return that column name.

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