Identify columns having all NULLs

How to find Columns having all NULLs in a Table?

There can be different ways and you can have a look on below code will identify columns having all NULLs. Similarly, you can write it to Delete such columns from Table if you don't want them in your table.

Declare

  l_count Number;

Begin

  For col In (Select Table_Name, Column_Name 

              From User_Tab_Columns Where Table_Name='EMP_DROP')

  Loop

    Execute Immediate 'Select Count(*) From '||col.Table_Name

                      ||' Where '||col.Column_Name

                      ||' Is Not Null And Rownum=1' Into l_count;

    If l_count = 0 then

      Dbms_Output.Put_Line('Column :'||col.column_name||' contains only Nulls');

    End If;

  End Loop;

End;

SQL> SELECT * FROM EMP_DROP;

        ID NAME           SALARY STATUS         DEPTNO  MANAGERID       COMM   ORDERNUM ORDERSTATU PRODUCTDES
---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
       101 Ravi            89000 Active                       103                       Pending
       104 Nikhil          69000 Active                       101                       Pending
       107 Karan          101000 Active                       112                       Pending
       108 Subho           78000 Active                       101                       Pending
       109 Manu           777000 Active                       103                       Pending
       110 Sajal           88000 Active                       101                       Pending
       112 Pankaj          90000 Active                       112                       Pending
       113 Abhishek        44000 Active                       110                       Pending
       115 Tom             89000 Active                       110                       Pending

9 rows selected.

SQL> SET SERVEROUTPUT ON
SQL> DECLARE
  2  l_count NUMBER;
  3  BEGIN
  4  FOR col IN(SELECT TABLE_NAME, COLUMN_NAME
  5             FROM USER_TAB_COLUMNS Where TABLE_NAME='EMP_DROP')
  6  LOOP
  7  EXECUTE IMMEDIATE 'SELECT Count(*) From '||col.TABLE_NAME
  8                     ||' Where '||col.COLUMN_NAME
  9                     ||' IS NOT NULL And ROWNUM=1' INTO l_count;
 10  IF l_count=0 Then
 11  DBMS_OUTPUT.PUT_LINE('Column :'||col.COLUMN_NAME||' Contains Only NULLs');
 12  END IF;
 13  END LOOP;
 14  END;
 15  /
Column :DEPTNO Contains Only NULLs
Column :COMM Contains Only NULLs
Column :ORDERNUM Contains Only NULLs
Column :PRODUCTDESC Contains Only NULLs

PL/SQL procedure successfully completed.

Get involved and leave your Comments in the Box Below. The more people get involved, the more we all benefit.

So, leave your thoughts before you leave the page.