Alter all tables in all database

Recently I've upgrade the mysql version to the latest 5.1.30.

I want to convert all collation into utf8_general_ci.

Googling around, I found script to list and alter all tables in just one particular database.

Modify it a bit. This is what came out. You can use and modified it to your needs. I hope it would be useful to you.

Warning: Run it with command line php! as it would generate script execution timeout on browser, especially on large list databases/tables (mine only 50 db and around 5k tables)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

<?php
mysql_connect("localhost","dbadminuser","dbadminpassword");
$dbs = mysql_query("SHOW DATABASES");
 
while ($baris = mysql_fetch_array($dbs))
{
foreach ($baris as $node => $db) {
mysql_select_db($db);
 
// convert code
$res = mysql_query("SHOW TABLES");
while ($row = mysql_fetch_array($res))
{
 foreach ($row as $key => $table)
 {
 mysql_query("ALTER TABLE " . $table . " CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci");
 echo $key . " =&gt; " . $table . " CONVERTED<br />";
 }
}
}}
?>