Jackymamouth Jackymamouth - 6 months ago 11
SQL Question

Procedure to add colums to all tables starting with prefix

I need to add a certain amount of colums to all tables starting with the prefix "art_".

If i do this manually it will take a long time as they are over 100 tables with this prefix.

Can i use a procedure to modify all these tables ? And if so, What would the procedure look like ?

Thanks in advance for any assistance

Answer

So you will need list of tables in an array.

Lets say : $table_names is an array with list of tables. If you have 100s of table. you can use a query to get list of table names too select * from information_schema.tables

foreach($table_names as $table_name)
{
    ALTER TABLE '$table_name'
    ADD COLUMN `art_col1` VARCHAR(12) NOT NULL AFTER `last_col`,
    ADD COLUMN `art_col2` VARCHAR(12) NOT NULL AFTER `col1`,
    ADD COLUMN `art_col3` VARCHAR(12) UNSIGNED NOT NULL AFTER `col2`;
}