Add new column for specific table of all existing databases in MySQL

To add column for specific table of all existing databases in MySQL.

Imaging if there are following databases in MySQL:

• client_1

• client_2

• client_3

• client_4

• client_5

And each database has "client" tables with "id" and "name" columns.

Lets add new column named "client_id" to "client" tables of all existing databases.

SELECT CONCAT("ALTER TABLE `", SCHEMA_NAME,"`.client ADD COLUMN client_id INT ;") FROM `information_schema`.`SCHEMATA` WHERE SCHEMA_NAME LIKE 'client_%';

I use

SCHEMA_NAME LIKE 'client_%'

condition to get database which started with "client_" to make sure to get correct databases.

If not it will add to all databases which has "client" table in MySQL. It is risky!

If you don’t want to accidentally perform actions against the "information_schema" or other internal MySQL tables, you can exclude them with following condition:

SCHEMA_NAME NOT IN ('information_schema', 'mysql', 'performance_schema')

#MySQL