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.