I have really big Oracle table with many columns that are not relevant to each filter that I use.
I want to write a query or function in Oracle that returns the name of the columns that have only null values or alternatively the name of the columns which are not null.
If you just want to find the columns that are always null you can run the query that this query creates, those columns that have a value 0 are null.
select 'select '
|| listagg('count(' || column_name || ') as ' || column_name, ', ')
within group (order by column_id)
|| ' from my_table;'
from user_tab_columns
where table_name = 'MY_TABLE'
Here's a SQL Fiddle to demonstrate.
If you want the names of the columns you have to use PL/SQL. This function will return a comma delimited list of column names, but of course you could return a user defined type etc.
create or replace function null_cols( P_TableName varchar2 ) return varchar2 is
l_cols varchar2(32767);
l_result number;
begin
for i in ( select column_name
from user_tab_columns
where table_name = upper(P_TableName)
) loop
execute immediate 'select count(' || i.column_name || ')
from ' || P_TableName
into l_result;
if l_result = 0 then
l_cols := l_cols || i.column_name || ', ';
end if;
end loop;
return l_cols;
end;
/
Here's an extension to the same SQL Fiddle with the function added.
I have to just add that if you're accepting user input in order to use the function you should use the dbms_assert package in order to help avoid SQL Injection.
dbms_output.put_line is limited to 255 characters so you then have to do something manually with it.You can use the following query to identify the same ,Please make sure you gather statistics for getting correct results
select table_name, column_name
from user_tab_cols
where table_name = 'MY_TABLE'
and NUM_DISTINCT = 0
and NUM_NULLS > 0
UPDATE 1:- IF your are gathering statistics regularly (not 100% statistics),you can use Ben's answer and also optimize it. The below would reduce the number of columns which needs to be checked
for i in ( select column_name
from user_tab_columns
where table_name = upper(P_TableName)
and num_distinct=0 and num_nulls > 0
) loop
