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.

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.

Leave a Reply

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

You are commenting using your 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 )

Connecting to %s