CODEWITHSUNDEEP
sqloracle-database
gilibi
gilibi

Reputation: 363

get column names of columns with only null values

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.

Upvotes: 0

Views: 4809

Answers (2)

psaraj12
psaraj12

Reputation: 5072

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

Upvotes: 0

Ben
Ben

Reputation: 52913

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.

Upvotes: 3

Related Questions