How can I select a column with no values?

Question

I have a table with thousands of rows and hundreds of columns (not my design). I'm trying to find out which columns are never used so I can simplify the table a little. How can I find which of the columns have no value in all rows?

Answer 1

Something like this, run from a *nix shell should give you an overview

for col in $(echo "explain  thetable" | mysql -s thedatabase | cut -f 1) ; do 
    echo "select \"$col\",count(*) from thetable where $col is not null" |mysql -s  thetable; 
done

You'll see the number of rows which is not null printed after each column, if there's 0 rows, every row has that column = null.

Answer 2

Check out PROCEDURE ANALYZE statement modifier. It is used for choosing a correct data type for columns and can be useful in your situation.