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.
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.
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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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!
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!
Glad to help.
(
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