MySQL - how to alter column on all databases with single action?

Question

I have a shared server where i have 50 to 60 databases each database has 200 tables.

How to add few new column's on all the existing databases in one go instead of one database after another manually?

ALTER TABLE  `users` ADD  `a` VARCHAR( 200 ) NULL;
ALTER TABLE  `users` ADD  `b` VARCHAR( 200 ) NULL;
ALTER TABLE  `users` ADD  `c` VARCHAR( 200 ) NULL;
ALTER TABLE  `users` ADD  `d` VARCHAR( 200 ) NULL;
ALTER TABLE  `users` ADD  `e` VARCHAR( 200 ) NULL;
ALTER TABLE  `users` ADD  `f` VARCHAR( 200 ) NULL;

You can't really. You could export the DB to a text file, and edit it in application code, but it might corrupt the data set, so I'd only ever attempt this with a backup to hand. — Strawberry, Aug 01 '17 at 07:19

score 0 · Accepted Answer · edited Sep 30 '17 at 09:34

You can create procedure for the same. And in procedure you can wrote cursor on this query.

select TABLE_NAME from INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='sampledata';

Make dynamic ALTER statement using Dynamic Query:

DELIMITER $$
CREATE PROCEDURE `SAMPLEPROCEDURE`(IN COLUMNNAME VARCHAR(40))
BEGIN
DECLARE VAR_TABLENAME VARCHAR(100);
DECLARE DONE  INT;

DECLARE CUR CURSOR FOR 
        SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'SAMPLEDATA';

DECLARE CONTINUE HANDLER FOR NOT FOUND SET DONE=1;

SET DONE = 0;
    OPEN CUR;
    TABLELOOP: LOOP
        FETCH CUR INTO VAR_TABLENAME;
        IF DONE = 1 THEN LEAVE TABLELOOP; END IF;
SET @VAR_ALTER_QUERY =CONCAT("ALTER TABLE ",VAR_TABLENAME," ADD ",COLUMNNAME," VARCHAR(200) NULL");

 PREPARE STMT FROM @VAR_ALTER_QUERY;
 EXECUTE STMT;
 DEALLOCATE PREPARE STMT;

    END LOOP TABLELOOP;

END

You can took above procedure as reference for same.

score 0 · Answer 2 · answered Aug 01 '17 at 07:36

use information_schema select case when table_schema is not null then CONCAT("USE ",TABLE_SCHEMA) end use_schema , CONCAT("Alter Table '", TABLE_SCHEMA,"'.'", TABLE_NAME, " Add 'a' varchar(200)") as MySQLCMD from TABLES where table_name = 'USERS';

The point is that you could use the dictionary schema to retrieve each 'user' tables from each schema and generate the ALTER scripts.

MySQL - how to alter column on all databases with single action?

2 Answers2