MySQL Forums
Forum List  »  General

Re: Alter all tables in a Database
Posted by: Jay Alverson
Date: January 26, 2009 12:08PM

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

Options: ReplyQuote


Subject
Written By
Posted
Re: Alter all tables in a Database
January 26, 2009 12:08PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.