Link to home
Start Free TrialLog in
Avatar of toooki
toooki

asked on

Find columns in Oracle table with null values

I have a table in Oracle (11gR2). The table is huge and has 125 fields.

Is there any single query that will print the column names of the table for which all the values are null.

I tried like:
select column_name from user_tab_columns where table_name = 'MYTAB1';
--assume I get f1, f2, F3 .. F125

select count(*) from MYTAB1 where F1 is not null;
select count(*) from MYTAB1 where F2 is not null;
select count(*) from MYTAB1 where F3 is not null;
...
select count(*) from MYTAB1 where F125 is not null;

If I get 0 value from any of the above 125 queries, I confirm all fields are null for that field. Could you please comment if I could automate it that would just return like:

F4
F9
F52
F97
F120


So the above fields have all empty values in the table. thank you.
Avatar of Pratima
Pratima
Flag of India image

Select X.ColName From
(
select count(*) as cnt, 'F1' as ColName from MYTAB1 where F1 is not null;
Union
select count(*) as cnt, 'F2' as ColName from MYTAB1 where F2 is not null;
Union
select count(*) as cnt, 'F3' as ColName from MYTAB1 where F3 is not null;
Union
...
select count(*) as cnt, 'F125' as ColName from MYTAB1 where F125 is not null;

) X
 Where X.cnt =0
Insert all the select statements


select X.ColName from (
select count(*) as cnt, 'F1' as ColName from MYTAB1 where F1 is not null
Union
select count(*) as cnt, 'F2' as ColName from MYTAB1 where F2 is not null
Union
select count(*) as cnt, 'F3' as ColName from MYTAB1 where F3 is not null
Union
select count(*) as cnt, 'F125' as ColName from MYTAB1 where F125 is not null

) X
where X.cnt = 0
ASKER CERTIFIED SOLUTION
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of toooki
toooki

ASKER

Thank you all very much.

The query slightwv posted above immediately gave me the column names I was looking for. I spent hours on it with some manual ways!! Many thanks!