How to list tables that contain null values in an oracle database?

Question

Which tables contain null values?
My logic was:

SELECT *
FROM all_tables;
WHERE table IS NULL;

Answer 1

Okay, first let’s understand what you mean by null, I believe there are two ways to interpret your question:

• A table without records
• A table whose column X is unregistered.

In the case of a table without records you can perform a query to see the number of records it has, something like:

select tab.owner as schema_name,
       tab.table_name
from sys.all_tables tab
where num_rows is null 
      or num_rows = 0
      -- excluding some Oracle maintained schemas
      and owner not in ('ANONYMOUS','CTXSYS','DBSNMP','EXFSYS', 'LBACSYS', 
      'MDSYS', 'MGMT_VIEW','OLAPSYS','OWBSYS','ORDPLUGINS', 'ORDSYS',
      'SI_INFORMTN_SCHEMA','SYS','SYSMAN','SYSTEM', 'TSMSYS','WK_TEST',
      'WKPROXY','WMSYS','XDB','APEX_040000', 'APEX_PUBLIC_USER','DIP', 
      'FLOWS_30000','FLOWS_FILES','MDDATA', 'ORACLE_OCM', 'XS$NULL',
      'SPATIAL_CSW_ADMIN_USR', 'SPATIAL_WFS_ADMIN_USR', 'PUBLIC',
      'WKSYS', 'OUTLN')
order by schema_name, 
         table_name;

Source

In the case of a table with null columns you can perform a query like:

SELECT COUNT(1) FROM tabela WHERE coluna IS NULL;

What you can do if you want, is run the DBMS_STATS gather_database_stats and then run the following query in the table ALL_TAB_COLUMNS:

SELECT * 
FROM ALL_TAB_COLUMNS

WHERE NULLABLE = 'Y' AND 
      NUM_DISTINCT = 0

Source of the idea