Tags

, , ,

Spending a good amount of time answering the questions on multiple Oracle forums, like OraFAQ, Stack Overflow, OTN, dBforums etc. I could now figure out the most common questions by amateurs in Oracle SQL and PL/SQL. My recent blog posts are focused on such questions. And one among them is “How to find all columns having at least a NULL value from all tables in the schema“.

Now, in SQL it would be a huge task to manually code all the column names in the WHERE clause to check IS NULL condition. However, the manual effort could be minimized to a great extent using an UDF(user defined function) and query on dynamic view ALL_TAB_COLUMNS. Let me demonstrate using the SCOTT schema :

  • Create a simple function FIND_NULL_COL, it would return 1 if found any NULL value in any column.
SQL> CREATE OR REPLACE FUNCTION FIND_NULL_COL(
  2      TABLE_NAME  VARCHAR2,
  3      COLUMN_NAME VARCHAR2)
  4    RETURN NUMBER
  5  IS
  6    cnt NUMBER;
  7  BEGIN
  8    CNT :=1;
  9    EXECUTE IMMEDIATE 'select count(*) from ' ||TABLE_NAME||' where '
 10                                              ||COLUMN_NAME||' is null'
 11    INTO cnt;
 12    RETURN
 13    CASE
 14    WHEN CNT > 0 THEN
 15      1
 16    ELSE
 17      0
 18    END;
 19  END;
 20  /

Function created.
  • Now, use the UDF in your SQL.
SQL> SELECT c.TABLE_NAME,
  2         c.COLUMN_NAME,
  3         FIND_NULL_COL(c.TABLE_NAME,c.COLUMN_NAME) null_status
  4  FROM all_tab_columns c
  5  WHERE C.OWNER    ='SCOTT'
  6  AND c.TABLE_NAME = 'EMP'
  7  ORDER BY C.OWNER,
  8    C.TABLE_NAME,
  9    C.COLUMN_ID
 10  /

TABLE_NAME COLUMN_NAME NULL_STATUS
---------- ----------- -----------
EMP        EMPNO                 0
EMP        ENAME                 0
EMP        JOB                   0
EMP        MGR                   1
EMP        HIREDATE              0
EMP        SAL                   0
EMP        COMM                  1
EMP        DEPTNO                0

8 rows selected.

So, NULL_STATUS 1 is the list of column names which has at least a NULL value.

If you do not want to create the function, then there is another way to get the list of columns having NULL value.

  • To get columns which are completely NULL.

Using the *_TAB_COLUMNS view. However, you MUST gather statistics before querying the view to get accurate results.

SQL> EXEC DBMS_STATS.gather_schema_stats('SCOTT');

PL/SQL procedure successfully completed.

SQL> SELECT t.table_name,
  2    t.column_name
  3  FROM user_tab_columns t
  4  WHERE t.nullable   = 'Y'
  5  AND t.num_distinct = 0;;

TABLE_NAME COLUMN_NAME
---------- -----------
BONUS      ENAME
BONUS      JOB
BONUS      SAL
BONUS      COMM
  • To get columns which have at least one NULL value.

Using the *_TAB_COLUMNS view. However, you MUST gather statistics before querying the view to get accurate results.

SQL> EXEC DBMS_STATS.gather_table_stats('SCOTT','EMP');

SQL> SELECT column_name,
  2    num_nulls
  3  FROM user_tab_columns
  4  WHERE NUM_NULLS <> 0
  5  AND table_name   ='EMP';

COLUMN_NAME  NUM_NULLS
----------- ----------
MGR                  1
COMM                11

Hope it helps!