MySQL Forums :: General :: Alter all tables in a Database

Maybe something like this ?

mysql> use information_schema;
Database changed
mysql> select TABLE_SCHEMA, TABLE_NAME from TABLES where TABLE_SCHEMA = "test";
+--------------+----------------------+
| TABLE_SCHEMA | TABLE_NAME           |
+--------------+----------------------+
| test         | a_psposts            | 
| test         | a_ththreads          | 
| test         | ab                   | 
| test         | accounts             | 
| test         | adotest              | 
| test         | baseball             | 
| test         | book                 | 
| test         | books                | 
| test         | candc                | 
| test         | cc                   | 
| test         | chapters             | 
| test         | colors               | 
| test         | contacts             | 
| test         | cult_inno            | 
| test         | daycount             | 
| test         | dict_sort            | 
| test         | dir2                 | 
| test         | dirduplicate         | 
| test         | directory            | 
| test         | dt                   | 
| test         | exceltable           | 
| test         | filenames            | 
| test         | hla                  | 
| test         | ids                  | 
| test         | import               | 
| test         | info                 | 
| test         | ip_customers         | 
| test         | lead                 | 
| test         | log_test             | 
| test         | mantis_bug_table     | 
| test         | mantis_contact_table | 
| test         | matches1             | 
| test         | measure              | 
| test         | mydates              | 
| test         | mysqlforums          | 
| test         | mysqlhtml            | 
| test         | mytable              | 
| test         | new_products         | 

| test         | test_getstring       | 
| test         | test_one             | 
| test         | testtable            | 
| test         | time_test            | 
| test         | times                | 
| test         | urltable             | 
| test         | user_msg             | 
| test         | xtest                | 
+--------------+----------------------+
73 rows in set (0.14 sec)

mysql> select CONCAT("Alter Table `", TABLE_SCHEMA,"`.`", TABLE_NAME, "` Add Column...blah...") as MySQLCMD from TABLES where TABLE_SCHEMA = "test";
+----------------------------------------------------------------+
| MySQLCMD                                                       |
+----------------------------------------------------------------+
| Alter Table `test`.`a_psposts` Add Column...blah...            | 
| Alter Table `test`.`a_ththreads` Add Column...blah...          | 
| Alter Table `test`.`ab` Add Column...blah...                   | 
| Alter Table `test`.`accounts` Add Column...blah...             | 
| Alter Table `test`.`adotest` Add Column...blah...              | 
| Alter Table `test`.`baseball` Add Column...blah...             | 
| Alter Table `test`.`book` Add Column...blah...                 | 
| Alter Table `test`.`books` Add Column...blah...                | 
| Alter Table `test`.`candc` Add Column...blah...                | 
| Alter Table `test`.`cc` Add Column...blah...                   | 
| Alter Table `test`.`chapters` Add Column...blah...             | 
| Alter Table `test`.`colors` Add Column...blah...               | 
| Alter Table `test`.`contacts` Add Column...blah...             | 
| Alter Table `test`.`cult_inno` Add Column...blah...            | 
| Alter Table `test`.`daycount` Add Column...blah...             | 

| Alter Table `test`.`orgs` Add Column...blah...                 | 
| Alter Table `test`.`parametervalue` Add Column...blah...       | 
| Alter Table `test`.`parts` Add Column...blah...                | 
| Alter Table `test`.`products` Add Column...blah...             | 
| Alter Table `test`.`products_sold` Add Column...blah...        | 
| Alter Table `test`.`psionics` Add Column...blah...             | 
| Alter Table `test`.`psiunlimited` Add Column...blah...         | 
| Alter Table `test`.`random_num1` Add Column...blah...          | 
| Alter Table `test`.`random_num2` Add Column...blah...          | 
| Alter Table `test`.`random_numbers` Add Column...blah...       | 
| Alter Table `test`.`rifts` Add Column...blah...                | 
| Alter Table `test`.`services_sold` Add Column...blah...        | 
| Alter Table `test`.`skills` Add Column...blah...               | 
| Alter Table `test`.`spells` Add Column...blah...               | 
| Alter Table `test`.`survey` Add Column...blah...               | 
| Alter Table `test`.`t` Add Column...blah...                    | 
| Alter Table `test`.`t_view` Add Column...blah...               | 
| Alter Table `test`.`table1` Add Column...blah...               | 
| Alter Table `test`.`tablea` Add Column...blah...               | 
| Alter Table `test`.`tableb` Add Column...blah...               | 
| Alter Table `test`.`tbl1` Add Column...blah...                 | 
| Alter Table `test`.`test` Add Column...blah...                 | 
| Alter Table `test`.`test_getstring` Add Column...blah...       | 
| Alter Table `test`.`test_one` Add Column...blah...             | 
| Alter Table `test`.`testtable` Add Column...blah...            | 
| Alter Table `test`.`time_test` Add Column...blah...            | 
| Alter Table `test`.`times` Add Column...blah...                | 
| Alter Table `test`.`urltable` Add Column...blah...             | 
| Alter Table `test`.`user_msg` Add Column...blah...             | 
| Alter Table `test`.`xtest` Add Column...blah...                | 
+----------------------------------------------------------------+
73 rows in set (0.16 sec)

mysql> notee;

Either use a TEE or SELECT INTO OUTFILE to build a .SQL command file.

Try it on a few tables for testing...

BACKUP YOUR DB/DATAFIRST

>

Thanks, Jay