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')