How can I find columns which have non-null values?

I have many columns in oracle database and some new are added with values. I like to find out which columns have values other tha开发者_Python百科n 0 or null. So I am looking for column names for which some sort of useful values exists at least in one row.

How do I do this?

Update: This sounds very close. How do I modify this to suit my needs?

select column_name, nullable, num_distinct, num_nulls
from all_tab_columns
where table_name = 'SOME_TABLE'

You can query all the columns using the dba_tab_cols view and then see if there are columns which have values other than 0 or null.

create or replace function f_has_null_rows(
    i_table_name in dba_tab_cols.table_name%type,
    i_column_name in dba_tab_cols.table_name%type
) return number is
v_sql varchar2(200);
v_count number;
begin
  v_sql := 'select count(*) from ' || i_table_name ||
           ' where ' || i_column_name ' || ' is not null and ' 
           || i_column_name  || ' <>0 ';

  execute immediate v_sql into v_count;

  return v_count;
end;
/


select table_name, column_name from dba_tab_Cols
where  f_has_null_rows (table_name, column_name) > 0 

1. If you have synonyms in some schemas, you mighty find some of the tables are repeated. You'll have to change the code to cater to that.

2. Also, the check "is not equal to zero" might not be valid for columns that are not integers and will give errors if columns are of date datatype. You'll need to add the conditions for those cases. use the Data_type column in dba_tab_cols and add the condition as needed.

Select Column_name
from user_tab_columns
where table_name='EMP' and num_nulls=0;

This finds columns which does not have any values so you can perform any actions to that.

Sorry, I misread the question the first time.

From this post on Oracle's forums

Assuming your stats are up to date:

SELECT t.table_name,
t.column_name
FROM user_tab_columns t
WHERE t.nullable = 'Y'
AND t.num_distinct = 0;

Will return you a list of table names and columns that are null. You might want to add something like:

AND t.table_name = upper('Your_table_name')

in there to limit the results to just your table.

select 'cats' as mycolumname from T 
    where exists (Select id from T where cats is not null)
union
select 'dogs' as mycolumnname from T 
    where exists (select id from T where dogs is not null)

# ad nauseam

is how to do it in SQL. EDIT: Different flavors of SQL might let you optimize with LIMIT or TOP 'n' in the subquery. Or maybe they're even smart enough to realize that EXIST() only needs one row and optimize silently/transparently. P.S. Add your test for zero to the subquery.