Wednesday, March 11, 2009

Rename multiple tables in MySQL database

Having to rename multiple tables with a similar pattern (prefix, suffix, ...) by replacing the recurring pattern, isn't an easy task, because there is no simple function or statement for doing it, not as far as I know.
Renaming tables like 'table_1234' and 'table_3456' to 'new_table_1234' and 'new_table_3456', is pretty straightforward in SQL:


RENAME TABLE `database`.`table_1234`
TO `database`.`new_table_1234`;
RENAME TABLE `database`.`table_3456`
TO `database`.`new_table_3456`;

or in one SQL statement :

RENAME TABLE
`database`.`table_1234` TO `database`.`new_table_1234`,
`database`.`table_3456` TO `database`.`new_table_3456`;

But when renaming a lot of tables, this method becomes tedious and time consuming.

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.

<?php
$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
$new_pattern = "new_pattern_"; // replacement string,
// can be empty

// login to MySQL server
$link = mysql_connect( $db_server, $db_username, $db_password);

if (!$link)
{
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 );
if (!$result)
{
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[0];
$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 );
if ($result_rename)
{
echo "Table `" . $table_name . "` renamed to :`";
echo $new_table_name . "`.\n";
$renamed++;
}
else
{
// notify when the renaming failed and show reason why
echo "Renaming of table `" . $table_name . "` has failed: ";
echo mysql_error( $link ) . "\n";
$failed++;
}
}

echo $renamed . " tables were renamed, " . $failed . " failed.\n";

// close connection to MySQL server
mysql_close( $link );
?>

18 comments:

Swift Arrow said...

Hey thank you so much for this!
I actually need to add a prefix, so I guess I'll just comment out the second line of the sql query to get all the tables.

Nowhere else have I found such a helpful post!

Dieter Adriaenssens said...

I'm glad my post was helpful to you.
If you're using this script to add a prefix, you probably have to modify the line where the table is renamed as well :

$new_table_name = str_replace ( $pattern, $new_pattern, $table_name);

has to become :

$new_table_name = $new_pattern . $table_name;

Swift Arrow said...

Yes, I saw that when I read through the script. It worked like a charm!

Unknown said...

THANKS!

it saves me a lot of work

Unknown said...

You are a genius! Thank you so much for this exceptional tool.

You should definitely distribute this nice script on more forums, especially on forum development sites;
http://www.phpbb.com
http://www.simplemachines.org/

I am absolutely positive that many many people are still looking for a script that would do what this script does.

Congratulations to you on your hard work and keep up the effort to help the community.

Anonymous said...

Excellent script! Saved me a ton of time; CMS Made Simple (a misnomer if I've ever seen one) uses a ton of tables, and renaming them all by hand would have sucked. Thank you!

Unknown said...

Extremely helpful code! I needed to add a prefix to tables that had none, so I modified the sql to: $new_table_name = str_replace ( $table_name, $new_pattern.$table_name, $table_name);

Nyds said...

Brilliant. Pure and simple.

crook in the business said...

Thanks man!! This multi rename tool works great!

Vayira said...

Thank you. This was very useful for changing my tables in Drupal.

Datshay Sherpa said...

Thanks ... i was trying to add prefix to my 127 tables. You saved my time...

writ of summons said...

Thanks, that totally worked! I used it to change the funny prefix on my WordPress tables.

Paul said...

great script! worked fine. thanks!

Giedrius Tuminauskas said...

well done, nicely written script :)

David Clothier said...

Genius! This saved me a stack of time, so thanks very much for posting this script!

Gary Cameron said...

I've been looking for something like this for a while.

I need an efficient way to move a WordPress blog from within one multi-site install to another and still keep all the theme and widget settings.

Gary Cameron said...

I have been looking for something like this for a while.

This will be very useful to me when migrating WordPress blogs from within one multi-site install to another.

SWYM_makesrobot said...

When it run the script
$pattern = "_CentralTable"; // search string
$new_pattern = "new_pattern_"; // replacement string,
// can be empty

// login to MySQL server
$link = mysqli_connect( $db_server, $db_username, $db_password);

if (!$link)
{
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 . "%'";

echo $echo

keep getting the string instead of the tables name
this is my terminal output:
SHOW TABLES FROM `c10mill2_edtopia` LIKE '%_CentralTable%'

My table is in the pattern user_CentalTable and need to change is to 1_CentralTable for all user likewise, pls help me where i am getting wrong.
Thanks