Category Archives: mysql

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;