How to find null and empty columns in a table with SQL

You're in luck - there's a fast and easy way to get this information using optimizer statistics.

After a large data load the statistics should be gathered anyway. Counting NULLs is something the statistics gathering already does. With the default settings since 11g, Oracle will count the number of NULLs 100% accurately. (But remember that the number will only reflect that one point in time. If you add data later, the statistics must be re-gathered to get newer results.)

Sample schema

create table test1(a number); --Has non-null values. create table test2(b number); --Has NULL only. create table test3(c number); --Has no rows. insert into test1 values(1); insert into test1 values(2); insert into test2 values(null); commit; 

Gather stats and run a query

begin dbms_stats.gather_schema_stats(user); end; / select table_name, column_name, num_distinct, num_nulls from user_tab_columns where table_name in ('TEST1', 'TEST2', 'TEST3'); 

Using the NUM_DISTINCT and NUM_NULLS you can tell if the column has non-NULLs (num_distinct > 0), NULL only (num_distinct = 0 and num_nulls > 0), or no rows (num_distinct = 0 and num_nulls = 0).

TABLE_NAME COLUMN_NAME NUM_DISTINCT NUM_NULLS ---------- ----------- ------------ --------- TEST1 A 2 0 TEST2 B 0 1 TEST3 C 0 0 

Certainly. Write a SQL script that:

• Enumerates all of the tables
• Enumerates the columns within the tables
• Determine a count of rows in the table
• Iterate over each column and count how many rows are NULL in that column.

If the number of rows for the column that are null is equal to the number of rows in the table, you've found what you're looking for.

Here's how to do just one column in one table, if the COUNT comes back as anything higher than 0 - it means there is data in it.

SELECT COUNT(<column_name>) FROM <table_name> WHERE <column_name> IS NOT NULL; 

This query return that what you want

 select table_name,column_name,nullable,num_distinct,num_nulls from all_tab_columns where owner='SCHEMA_NAME' and num_distinct is null order by column_id; 

Below script you can use to get empty columns in a table

 SELECT column_name FROM all_tab_cols where table_name in (<table>) and avg_col_len = 0; 

The following script can be used to get tables with no rows and empty columns, alternatively you can use the dba_tables and dba_tab_cols views if you have the privileges:

DECLARE v_owner VARCHAR(100) := 'SCHEMA_NAME'; v_count NUMBER; v_smt VARCHAR2(500); BEGIN FOR t IN (SELECT * FROM all_tables t WHERE owner = v_owner ORDER BY owner, table_name) LOOP v_smt := 'SELECT COUNT(*) FROM ' || t.owner || '.' || t.table_name; EXECUTE IMMEDIATE v_smt INTO v_count; IF v_count = 0 THEN dbms_output.put_line('TABLE ' || t.owner || '.' || t.table_name || ' IS EMPTY'); ELSE FOR c IN (SELECT * FROM all_tab_cols c WHERE owner = v_owner AND c.table_name = t.table_name) LOOP v_smt := 'SELECT COUNT(*) FROM ' || c.owner || '.' || c.table_name || ' WHERE ' || c.column_name || ' IS NOT NULL'; EXECUTE IMMEDIATE v_smt INTO v_count; IF v_count = 0 THEN dbms_output.put_line('COLUMN ' || c.owner || '.' || c.table_name || '.' || c.column_name || ' IS EMPTY'); END IF; END LOOP; END IF; END LOOP; END;