CODEWITHSUNDEEP
sqloracle-database
Suresh Dangeti
Suresh Dangeti

Reputation: 21

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

I am using Oracle SQL developer, We are loading tables with data and I need to validate if all the tables are populated and if there are any columns that are completely null(all the rows are null for that column).

For tables I am clicking each table and looking at the data tab and finding if the tables are populated and then have looking through each of the columns using filters to figure out if there are any completely null columns. I am wondering if there is faster way to do this.

Thanks, Suresh

Upvotes: 1

Views: 19698

Answers (6)

Carlos Brítez
Carlos Brítez

Reputation: 1

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;

Upvotes: 0

wfolkerts
wfolkerts

Reputation: 107

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;

Upvotes: 0

Tigran Shahnazaryan
Tigran Shahnazaryan

Reputation: 178

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;

Upvotes: 1

Jon Heller
Jon Heller

Reputation: 36807

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

Upvotes: 3

John Mitchell
John Mitchell

Reputation: 472

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;

Upvotes: 1

David Hoelzer
David Hoelzer

Reputation: 16331

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.

Upvotes: 2

Related Questions