How can I alter multiple tables at once in mysql?

Question

I am trying to alter multiple tables and change the size of the username VARCHAR column to 999 as its current size is too small and now things are screwed up. How can I do this?

I have tried the following and it worked for one table but when trying to update multiple table names it returned errors:

ALTER TABLE  `TABLE_NAME` CHANGE `username` VARCHAR( 999 ) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL

@julesanchez it is a syntax error, but because I'm working with so many tables the error is too long. And @jakx I would like to `ALTER` for each table but can't seem to do it. — John Doe, Jan 19 '12 at 23:21

score 10 · Accepted Answer · edited Oct 01 '15 at 16:27

10

You can't do it with a single query. You need to query information_schema views to get the list of tables and columns to change. You will then use the resulting resultset to create ALTER queries (either in an external application/script or within MySQL using cursors and prepared statements)

edited Oct 01 '15 at 16:27

pelumi

1,530
12
21

answered Jan 19 '12 at 23:19

Mchl

61,444
9
118
120

How can I query `information_schema`? Could you show me an example? – John Doe Jan 19 '12 at 23:24
1

http://stackoverflow.com/questions/193780/how-to-find-all-the-tables-in-mysql-with-specific-column-names-in-them/193860#193860 – Ken Jan 20 '12 at 06:57

score 3 · Answer 2 · answered May 07 '12 at 22:56

3

I found the only way to do this was via an external file. This is my implimentation :

function changeSchema($oldName, $newName, $type, $len)
{
    $res = mysql_query("SELECT DISTINCT TABLE_NAME
        FROM INFORMATION_SCHEMA.COLUMNS
        WHERE COLUMN_NAME = '$oldName' AND 
        TABLE_SCHEMA = 'your_database_name'");
    if($res)
        while($line=mysql_fetch_object($res))
            mysql_query("ALTER TABLE `$line->TABLE_NAME` CHANGE `$oldName` `$newName` $type( $len ) NOT NULL ");
    }
}

I then was able to modify any table I wanted easily.

answered May 07 '12 at 22:56

Raath

689
1
6
21

is this php or a function that used in query ? – Gagantous May 08 '18 at 20:42
1

@Gagantous That is definitely PHP and Raath posted in 2012 which is likely why he wasn't using MySQLi yet. ;-) – John Oct 06 '19 at 05:37

score 1 · Answer 3 · answered Jan 20 '12 at 06:54

1

Write a query file to alter all tables and execute that file.

answered Jan 20 '12 at 06:54

Naveen Kumar

4,543
1
18
36

How can I alter multiple tables at once in mysql?

3 Answers3

Linked