Find all those columns which have only null values, in a MySQL table

Question

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.

Answer 1

You can avoid using a procedure by dynamically creating (from the INFORMATION_SCHEMA.COLUMNS table) a string that contains the SQL you wish to execute, then preparing a statement from that string and executing it.

The SQL we wish to build will look like:

SELECT * FROM (   SELECT 'tableA' AS `table`,          IF(COUNT(`column_a`), NULL, 'column_a') AS `column`   FROM   tableA UNION ALL   SELECT 'tableB' AS `table`,          IF(COUNT(`column_b`), NULL, 'column_b') AS `column`   FROM   tableB UNION ALL   -- etc. ) t WHERE `column` IS NOT NULL 

This can be done using the following:

SET group_concat_max_len = 4294967295; -- to overcome default 1KB limitation  SELECT CONCAT(          'SELECT * FROM ('        ,  GROUP_CONCAT(             'SELECT ', QUOTE(TABLE_NAME), ' AS `table`,'           , 'IF('           ,   'COUNT(`', REPLACE(COLUMN_NAME, '`', '``'), '`),'           ,   'NULL,'           ,    QUOTE(COLUMN_NAME)           , ') AS `column` '           , 'FROM `', REPLACE(TABLE_NAME, '`', '``'), '`'           SEPARATOR ' UNION ALL '          )        , ') t WHERE `column` IS NOT NULL'        ) INTO   @sql FROM   INFORMATION_SCHEMA.COLUMNS WHERE  TABLE_SCHEMA = DATABASE();  PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt; 

See it on sqlfiddle.