Mysql Drop column where all row value is null

Question

How can i drop all columns from a table where all its values are null ?

i have a huge table (100k+ rows) with around 20+ columns, lots of these columns are not used at all, so i want to remove all columns that is NULL in all rows

`delete from table where col1 is null and col2 is null and col3 is null ... ` repeat for all the columns — ajreal, Oct 13 '14 at 01:45
@ajreal i dont want to delete rows, i want to drop the column it self — Zalaboza, Oct 13 '14 at 01:47

score 1 · Answer 1 · answered Oct 13 '14 at 01:56

You could loop through each field in a cursor and perform the check and ALTER in one fell swoop, or you can just run a check in one step to see which fields aren't used:

SELECT MAX(col1) col1
      ,MAX(col2) col2
      ,MAX(col3) col3
      .....
FROM YourTable

Then remove any fields that have a MAX() of NULL:

ALTER TABLE YourTable DROP col2

Mysql Drop column where all row value is null

1 Answers1

Linked