Oracle: Count non-null fields for each column in a table

I need a query to count the total number of non-null values for each column in a table. Since my table has hundreds of columns I’m looking for a solution that only requires me to input the table name.

Perhaps using the result of:

select COLUMN_NAME from ALL_TAB_COLUMNS where TABLE_NAME='ORDERS';

to get the column names and then a subquery to put counts against each column name? The additional complication is that I only have read-only access to the DB so I can’t create any temp tables.

Slightly out of my league with this one so any help is appreciated.

Answer

Construct the query in SQL or using a spreadsheet. Then run the query.

For instance, assuming that your column names are simple and don’t have special characters:

select replace('select ''[col]'', count([col]) from orders union all ',
               '[col]', COLUMN_NAME
              ) as sql
from ALL_TAB_COLUMNS
where TABLE_NAME = 'ORDERS';

(Of course, this can be adapted for more complex column names, but I’m trying to show the idea.)

Then copy the code, remove the final union all and run it.

You can put this in one string if there are not too many columns:

select listagg(replace('select ''[col]'', count([col]) from orders',
                       '[col]', COLUMN_NAME
                      ), ' union all '
               ) within group (order by column_name) as sql
from ALL_TAB_COLUMNS
where TABLE_NAME = 'ORDERS';

You can also use execute immediate using the same query, but that seems like overkill.