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.