How to change multiple tables at once in mysql?

Question

I am trying to resize several tables and resize the VARCHAR username column to 999, as its current size is too small, and now everything went wrong. How can i do this?

I tried the following and it worked for a single 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 

Answer 1

You cannot do this with a single request. You need to request information_schema views to change the list of tables and columns. Then you will use the result set to create ALTER queries (either in an external / script application or in MySQL using cursors and prepared statements)

Answer 2

I found the only way to do this through an external file. This is my explanation:

 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 "); } } 

Then I was able to change any table that I wanted easily.

Answer 3

Write a query file to modify all tables and execute this file.