Find all those columns which have only null values, in a MySQL table
- by Robin v. G.
The situation is as follows:
I have a substantial number of tables, with each a substantial number of columns. I need to deal with this old and to-be-deprecated database for a new system, and I'm looking for a way to eliminate all columns that have - apparently - never been in use.
I wanna do this by filtering out all columns that have a value on any given row, leaving me with a set of columns where the value is NULL in all rows. Of course I could manually sort every column descending, but that'd take too long as I'm dealing with loads of tables and columns. I estimate it to be 400 tables with up to 50 (!) columns per table.
Is there any way I can get this information from the information_schema?
EDIT:
Here's an example:
column_a column_b column_c column_d
NULL NULL NULL 1
NULL 1 NULL 1
NULL 1 NULL NULL
NULL NULL NULL NULL
The output should be 'column_a' and 'column_c', for being the only columns without any filled in values.
