Drop empty columns in MySQL

Ever have a table with a ton of columns some of which aren’t used?

Below is a procedure to drop empty columns from a MySQL table:

CREATE PROCEDURE dropEmptyColumns(dbName varchar(64), tableName varchar(64), drop_log text)
LANGUAGE SQL
DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT ''
BEGIN

  DECLARE done INT DEFAULT FALSE;
  DECLARE col_name varchar(256);

  -- Get Column Names
  DECLARE column_cur CURSOR FOR
      SELECT COLUMN_NAME
      FROM `information_schema`.`COLUMNS`
        WHERE (`TABLE_SCHEMA` = dbName)
          AND (`TABLE_NAME` = tableName);
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

  SET @amount = -1;
  SET @tableName = tableName;
  SET @dbname = dbName;
  SET @drop_log = '';
  SET @testSql = '';

  -- For each column name
  OPEN column_cur;
  check_column_loop: LOOP

    -- SELECT 'IN LOOP' INTO @drop_log;
    FETCH column_cur INTO col_name;

    -- Break loop if no more rows
    IF done THEN
      LEAVE check_column_loop;
    END IF;
    
    -- Build the query, with dynamic table and column. Store the result in @amount
    SELECT CONCAT('SELECT COUNT(', col_name ,') INTO @amount FROM ', @tableName ,' WHERE(', col_name ,' IS NOT NULL OR ', col_name ,' != "");') INTO @testSql;
    PREPARE testSqlStmt FROM @testSql;

    -- Execute
    EXECUTE testSqlStmt;

    -- Check if we have 0 rows, else we still have data and we do not want to drop.
    IF @amount = 0 THEN
        
      -- Build the query
      SELECT CONCAT('ALTER TABLE ', @tableName ,' DROP COLUMN ', col_name ,';') INTO @alterSql;

      -- Execute
      PREPARE alterSqlStmt FROM @alterSql;
      EXECUTE alterSqlStmt;
                
      -- Append Log
      SELECT CONCAT(@drop_log, col_name, ' DROPPED\r\n') INTO @drop_log;

    END IF;

  END LOOP;
  CLOSE column_cur;

END

Once you have created the procedure you can run it like this:

CALL dropEmptyColumns('<DB_NAME>', '<TABLE_NAME>', @drop_log);
SELECT @drop_log;

One thought on “Drop empty columns in MySQL”

  1. Hi and thanks,What about if you want your stoerd procedure to update a different column, which is passed in; e.g. in the following code I want to set “update_field” dynamically. CREATE PROCEDURE `spKRHasDuplicate`( in v_staff_no int ,in update_field varchar(50) ,out has_duplicate smallint ) BEGIN SELECT v_staff_no as staff_no, case when ( SELECT count(staff_no) as has_duplicate FROM temp_employees_2 WHERE staff_no = v_staff_no group by staff_no ) > 1 then 1 else 0 end as has_duplicate; SET has_duplicate = has_duplicate; UPDATE temp_employees_2 SET update_field=update_field WHERE staff_no = v_staff_no;

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>