CODEWITHSUNDEEP
sqloracle-database
TaiwanTimmy
TaiwanTimmy

Reputation: 187

NUM_NULLS and NUM_ROWS not work for Oracle

I am trying to find the number of total rows, and the number of null rows for each column in a database. I feel like the following should work, but each time I run it the num_nulls and num_rows came back blank (So I put in the COALESCE so it now results in a 0)

SELECT atc.column_name, atc.table_name, atc.data_type, COALESCE(atc.num_nulls, 0), COALESCE(at.num_rows, 0) FROM all_tab_columns atc JOIN all_tables at ON ATC.TABLE_NAME = at.table_name ;

Is there something wrong with my code or is it possible the all_tab_columns or all_tables are not up to date?

PS. The tables do have information in them, I have already checked to see if the database was empty.

Upvotes: 1

Views: 1774

Answers (1)

Craig
Craig

Reputation: 5820

You can loop over the all_tab_columns table to generate a script for you that looks something like this:

with data as (
    select
        count(*) tot_rows,
        count(owner) owner,
        count(object_name) object_name,
        count(subobject_name) subobject_name
    from tblname
)
select column_name, not_null_count, tot_rows - not_null_count null_count
from data
unpivot (
    not_null_count
    for column_name in (owner,object_name,subobject_name)
);

Which will give you the results:

COLUMN_NAME    NOT_NULL_COUNT NULL_COUNT
-------------- -------------- ----------
OWNER                     801          0
OBJECT_NAME               801          0
SUBOBJECT_NAME              0        801

Upvotes: 1

Related Questions