Rename All Tables in MySQL

One of the products I support is the Kayako support suite.  While undergoing the upgrade from version 3 to version 4, I wanted to remove the prefix on each table.  Of course, I didn’t want to rename every table manually.  By doing some googling, I found some code that I tweaked to do my bidding.

SELECT CONCAT('RENAME TABLE `', TABLE_SCHEMA, '`.`', TABLE_NAME, '` TO `', TABLE_SCHEMA, '`.`prefix_', TABLE_NAME, '`;')
FROM `TABLES` WHERE `TABLE_SCHEMA` = "test_database_name";

First, as always do a backup.

Then, run this on the information_schema schema.  It will generate a list of all the tables with the rename table commands.  After it’s generated, copy the results into notepad, replace prefix_ with whatever you wish and re-run it on the information_schema schema.

Wallah!  You just renamed all your tables.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s