How to add a prefix to tables in a MySQL Schema?

I have recently had to change a database from one schema to another. Unfortunately, I have also had to change the prefix to bring some order in the tables in that specific destination schema.

In order to add a prefix to a set of tables within an existing schema, you will need to perform a few simply MySQL operations calling the ALTER and RENAME functions

In your MySQL editor (MySQL GUI Admin Tools, Workbench or PhpMyAdmin), type the following in the SQL script window:

Change urprefix and urDBName in the code below

SET group_concat_max_len=100000;

SELECT GROUP_CONCAT(CONCAT("ALTER TABLE ", table_name, " RENAME TO urprefix_", table_name) SEPARATOR ";") 
FROM information_schema.tables 
WHERE table_schema = "urDBName";

Note, you might want to take notes of the number tables returned in your original schema as well as the number of tables existing in your destination schema (for validation).

Note, you might have to clean some of the output using a tool like Notepad++ (removing (“ and “), for example)

Now, copy and paste the output back into the SQL script window

It should look something like this:

ALTER TABLE table1 RENAME TO urprefix_table1;
ALTER TABLE table1log RENAME TO urprefix_table1log;
ALTER TABLE table2 RENAME TO urprefix_table2;
ALTER TABLE table2_aid RENAME TO urprefix_table2_aid;
ALTER TABLE table3 RENAME TO urprefix_table3;
...

Click on GO

If you have done everything right, you should now have all tables for your given scheme renamed with a new prefix “urprefix_”.