The script below was posted on http://ruleant.blogspot.com/2009/03/rename-multiple-tables-in-mysql.html# by Dieter Adriaenssens .
This PHP script automates the renaming of multiple tables in a MySQL database. It lists all tables in a MySQL database, which contain a defined string pattern. The script creates and executes a series of SQL statements, which rename the table by replacing the search pattern in the original table name with another pattern in the new table name.
This script can easily be modified to rename multiple databases, or when stripping the original pattern (f.e. a prefix) and adding a new pattern (f.e. a suffix) is needed.
$db_server = "localhost"; // hostname MySQL server
$db_username = "username"; // username MySQL server
$db_password = "password"; // password MySQL server
$db_name = "database"; // database name
$pattern = "pattern_"; // search string for example: "drpl_"
$new_pattern = "new_pattern_"; // replacement string.
// can be empty. For example "" for no db prefix
// login to MySQL server
$link = mysql_connect( $db_server, $db_username, $db_password);
die('Could not connect: ' . mysql_error());
// list all tables in the database containing the search pattern
$sql = "SHOW TABLES FROM `" . $db_name . "`";
$sql .= " LIKE '%" . $pattern . "%'";
$result = mysql_query ( $sql, $link );
die("Invalid query: " . mysql_error( $link ));
$renamed = 0;
$failed = 0;
while ( $row = mysql_fetch_array ($result) )
// rename every table by replacing the search pattern
// with a new pattern
$table_name = $row;
$new_table_name = str_replace ( $pattern, $new_pattern, $table_name);
$sql = "RENAME TABLE `" . $db_name . "`.`" . $table_name . "`";
$sql .= " TO `" . $db_name . "`.`" . $new_table_name . "`";
$result_rename = mysql_query ( $sql, $link );
echo "Table `" . $table_name . "` renamed to :`";
echo $new_table_name . "`.\n";
// notify when the renaming failed and show reason why
echo "Renaming of table `" . $table_name . "` has failed: ";
echo mysql_error( $link ) . "\n";
echo $renamed . " tables were renamed, " . $failed . " failed.\n";
// close connection to MySQL server
mysql_close( $link );
After running the script, you'll have to edit your settings.php to rename the db_prefix setting. After the script is run, you may get a pdo error: "PDOException: SQLSTATE[42S02]: Base table or view not found: 1146 Table '[database].semaphore' doesn't exist ... in lock_may_be_available()". If this happens: 1) rename your settings.php to something like settings-old.php; 2) copy the default.settings.php to the sites/default folder and rename it to settings.php. This will cause the install script to run. When prompted for your database settings, enter the name of your existing database. The install script will inform you that the database already exists and offer to take you to your site.