What query would return the name of the columns of a table where all rows are NULL?
- Anybody can ask a question
- Anybody can answer
- The best answers are voted up and rise to the top
In addition you can get an approximate answer by querying the catalog - if
In Postgresql, you can get the data directly from the stats:
You might get a few false positives, so a recheck is in order after finding the candidates.
I will show you my solution in T-SQL, working for SQL Server 2008. I'm not familiar with PostgreSQL, but I hope that you'll find some guidance in my solution.
What I did, in short, was to create a test table with 5 columns, ID and testTime being generated by identity and getdate() function, while the 3 varchar columns being the ones of interest. One will have only NULL values, one will not have any NULLs, the other will be a combined column. The final result of the script will be that the script will report the column nullColumn as having all rows NULL.
The idea was to calculate the function DATALENGTH for each column (calculates the number of bytes for a given expression). So I calculated the DATALENGTH value for each row of each column and made a SUM per column. If the SUM per column is NULL, then the complete column has NULL rows, otherwise there is some data inside.
Now you have to find the translation for PostgreSQL and hopefully a colleague will be able to help you with that. Or maybe there is a nice system view that will show how dumb I am for reinventing the wheel :-).
You need to query the information catalog for such information:
gives you the matching tables for your columns.
I don't have a postgres installation currently at hand but the rest should be simple