Identify columns with NULL values

Learn how to identify the columns present with NULL values in the table with example queries.

There can be different ways to identify the columns having NULL values.
Below are the few queries to find the NULL value columns in the table.

Below is the procedure to find the Columns having all NULL values

CREATE OR REPLACE PROCEDURE NULL_COLUMNS_TEST_PROC AS
  CURSOR CREC IS
    SELECT t.column_name
      FROM user_tab_columns t
     WHERE t.table_name = 'EMP1';

  V_SQL VARCHAR2(3000);
  V_CNT NUMBER;
BEGIN
  FOR I IN CREC LOOP
    V_SQL := 'SELECT COUNT(1) FROM EMP1 WHERE '||  I.column_name||
             ' IS  NOT NULL ';
    EXECUTE IMMEDIATE V_SQL
      INTO V_CNT;

    IF V_CNT  = 0 THEN
      DBMS_OUTPUT.put_line(I.column_name);
    END IF;

  END LOOP;

END;

Below is the procedure to find the Columns at least one null value

CREATE OR REPLACE PROCEDURE NULL_COLUMNS_TEST_PROC AS
  CURSOR CREC IS
    SELECT t.column_name
      FROM user_tab_columns t
     WHERE t.table_name = 'EMP1';

  V_SQL VARCHAR2(3000);
  V_CNT NUMBER;
BEGIN
  FOR I IN CREC LOOP
    V_SQL := 'SELECT COUNT(1) FROM EMP1 WHERE '||  I.column_name||
             ' IS  NULL ';
    EXECUTE IMMEDIATE V_SQL
      INTO V_CNT;

    IF V_CNT  > 0 THEN
      DBMS_OUTPUT.put_line(I.column_name);
    END IF;

  END LOOP;

END;

Below is the procedure to find the Columns having no NULL values

CREATE OR REPLACE PROCEDURE NULL_COLUMNS_TEST_PROC AS
  CURSOR CREC IS
    SELECT t.column_name
      FROM user_tab_columns t
     WHERE t.table_name = 'EMP1';

  V_SQL VARCHAR2(3000);
  V_CNT NUMBER;
BEGIN
  FOR I IN CREC LOOP
    V_SQL := 'SELECT COUNT(1) FROM EMP1 WHERE '||  I.column_name||
             ' IS   NULL ';
    EXECUTE IMMEDIATE V_SQL
      INTO V_CNT;

    IF not V_CNT  > 0 THEN
      DBMS_OUTPUT.put_line(I.column_name);
    END IF;

  END LOOP;

END;