Add a column to multiple tables in mysql

In this post I’m going to explain how to add a column to multiple tables in a mysql database.

Although it might sound a bit weird, recently I came across a requirement to add a column to a few dozen of tables in a mysql database. Here’s the main steps needed to achieve this followed by the complete code.

STEP 1.  Get the names of the tables

First we need to select the names of the tables that we are going to modify and here’s how

    SELECT * FROM `information_schema`.`tables` WHERE table_schema = 'db_name' AND table_name LIKE 'mytables_%';

STEP 2. Create a cursor to iterate over the table names

Now  create a cursor on the above query so that we can iterate over the table names and run our query to add the column

    DECLARE column_cursor CURSOR FOR
    SELECT * FROM `information_schema`.`tables` WHERE table_schema = 'db_name' AND table_name LIKE 'mytables_%';

STEP 3. Iterate the cursor over the table names and prepare the sql statement to add the column to the tables

Now we”ll iterate the cursor and prepare an sql statement with the alter table statement to add the column

    SET @prepstmt = CONCAT('ALTER TABLE adtracker','.',v_table,'  ADD COLUMN id INT AUTO_INCREMENT NOT NULL;');
    PREPARE stmt FROM @prepstmt;

STEP 4. Execute the created statement

Execute the prepared statement to add the columns to the tables

    EXECUTE stmt;

Those are the main steps in the solution but in the actual code we have to do a bit more coding to declare the cursor, loops, etc… so here’s the fully completed working code of the solution. Note that this is created as a stored procedure as the cursors cannot be used in regular sql queries.

Here we go:

DELIMITER $$

USE `db_name`$$

DROP PROCEDURE IF EXISTS `alter_test_1`$$

CREATE DEFINER=`db_name`@`10.%` PROCEDURE `alter_test_1`()
BEGIN

    DECLARE v_finished INTEGER DEFAULT 0;
    DECLARE v_table VARCHAR(100) DEFAULT "";
    DECLARE stmt VARCHAR(500) DEFAULT "";

    DECLARE column_cursor CURSOR FOR
    SELECT * FROM `information_schema`.`tables` WHERE table_schema = 'db_name' AND table_name LIKE 'mytables_%';

    DECLARE CONTINUE HANDLER
    FOR NOT FOUND SET v_finished = 1;

    OPEN column_cursor;

    alter_tables: LOOP

        FETCH column_cursor INTO v_table;

        IF v_finished = 1 THEN
        LEAVE alter_tables;
        END IF;

        SET @prepstmt = CONCAT('ALTER TABLE adtracker','.',v_table,'  ADD COLUMN id INT AUTO_INCREMENT NOT NULL;');
        PREPARE stmt FROM @prepstmt;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;

    END LOOP alter_tables;

    CLOSE column_cursor;

END$$

DELIMITER ;

Happy coding 🙂