Skip navigation links

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


Advanced Search

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
Alter all tables in a Database Nelson Dawson 01/26/2009 08:47AM
Re: Alter all tables in a Database Jay Alverson 01/26/2009 12:08PM
Re: Alter all tables in a Database Nelson Dawson 01/27/2009 10:33AM
Re: Alter all tables in a Database Jay Alverson 01/27/2009 02:35PM
Re: Alter all tables in a Database Nelson Dawson 01/28/2009 02:46AM


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.