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

Solution 1

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)

Solution 2

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.

Solution 3

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

Comments

[JuSchz]

Can we see the detail of the error ?

[jakx]

Can't you do this one by one or write a script to do it?

[John Doe]

@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.

[JuSchz]

Maybe you can try with 2 tables, and display the error ?

[John Doe]

How can I query information_schema? Could you show me an example?

[Ken]

stackoverflow.com/questions/193780/…

[Gagantous]

is this php or a function that used in query ?

[John]

@Gagantous That is definitely PHP and Raath posted in 2012 which is likely why he wasn't using MySQLi yet. ;-)