SQL & PL/SQL

SQL> create table sample1
  2  as select 1 col1, null col2 from dual
  3  union all
  4  select 2, null from dual
  5  union all
  6  select null, 3 from dual
  7  /

Table created.

SQL> select column_name, num_nulls
  2  from user_tab_col_statistics
  3  where table_name = 'SAMPLE1'
  4  /

no rows selected

SQL> execute dbms_stats.gather_table_stats(user, 'SAMPLE1');

PL/SQL procedure successfully completed.

SQL> select column_name, num_nulls
  2  from user_tab_col_statistics
  3  where table_name = 'SAMPLE1'
  4  /

COLUMN_NAME                     NUM_NULLS
------------------------------ ----------
COL1                                    1
COL2                                    2

SQL> 

Cheers
Sarma.

Hi Sarma,
Thank you for your quick response.
Your solution looks fine , but i dont want to recollect statistics on my production database with noreason.
Could you please suggest another option ?
Thanks.

You can write a select which 'counts' each column for number of NON_NULL. Something like:

select 'select count(*), '|| column_name || ' from ' || table_name || ' where ' || column_name || ' is not null;' from user_tab_cols s where s.table_name = 'your_table';

and then:

run the output selects on their own. Wherever the counts are zero, there is your column.

Message was edited by:
RPuttagunta

You can use Dynamic SQL to get the column_names from user_tab_columns and then for each columns execute the null check and get the count.
Your approach is not correct. With Production database, you OUGHT to have statistics up-to-date. If you have statistics already available, then skip the collection of statistics and just use my SQL.

Cheers
sarma.