How to select columns from a table which have non null values?

Question

Have a look as statistics information, it may be useful for you:

SQL> exec dbms_stats.gather_table_stats('SCOTT','EMP');

PL/SQL procedure successfully completed.

SQL> select num_rows from all_tables where owner="SCOTT" and table_name="EMP";

  NUM_ROWS
----------
        14

SQL> select column_name,nullable,num_distinct,num_nulls from all_tab_columns
  2  where owner="SCOTT" and table_name="EMP" order by column_id;

COLUMN_NAME                    N NUM_DISTINCT  NUM_NULLS
------------------------------ - ------------ ----------
EMPNO                          N           14          0
ENAME                          Y           14          0
JOB                            Y            5          0
MGR                            Y            6          1
HIREDATE                       Y           13          0
SAL                            Y           12          0
COMM                           Y            4         10
DEPTNO                         Y            3          0

8 rows selected.

For example you can check if NUM_NULLS = NUM_ROWS to identify “empty” columns.
Reference: ALL_TAB_COLUMNS, ALL_TABLES.

Leave a Comment Cancel reply