How to rename all tables in a Mysql database

Question

I have a Mysql database with several tables and am migrating to another database.

I have Client and must stay Cliente_pe

I have product and should stay

How can I accomplish this without however stopping to rename each of the 50 tables?

Answer 1

To rename a table, you use the command

RENAME TABLE nomeOriginal TO nomeNovo

As there are many tables, you can use this query to generate all RENAMEs automatically:

SELECT
   CONCAT(
      'RENAME TABLE `', table_schema, '`.`', table_name,
      '` TO `', table_schema, '`.`', table_name, '_PE`;' )
FROM
   INFORMATION_SCHEMA.TABLES WHERE table_schema="NomeDoDb";

Output example:

RENAME TABLE `NomeDoDb`.`eventos` TO `NomeDoDb`.`eventos_PE`;
RENAME TABLE `NomeDoDb`.`fotos` TO `NomeDoDb`.`fotos_PE`;
RENAME TABLE `NomeDoDb`.`usuarios` TO `NomeDoDb`.`usuarios_PE`;

Then just take the result and run as a new query, either via copy & Paste, redirecting to an SQL script, etc.

Perks:

• It puts a Suffix in tables, but can be easily adapted to prefixes or mixed;
• Avoid manually typing all table names;
• Easy to delete a group of tables by filtering on WHERE or manually;
• Easy to work on more than one DB using the clause WHERE;
• Does not depend on specific tool, can be used in command line, or even in visual tools like Mysql Workbench.
• If you need to do this periodically, you can create a PROCEDURE, and use whenever necessary, example: AdicionarSufixo( 'MeuDb', '_PE' );

Answer 2

The easy way, since 50 is a not so great number of lines:

RENAME TABLE
    Cliente TO Cliente_PE,
    Produto TO Produto_PE,
    TabelaX TO TabelaX_PE

_{See the sql manual.}

You can also do this using the phpmyadmin, but then you will be adding a prefix to the tables (Pe_client, etc):

1. Select the database containing the tables in the side menu.
2. Check the checkbox Check All.
3. In the select on the side select Add prefix to table.
4. In the "Add prefix" field type prefix_, in your case, PE_.

Answer 3

In phpmyadmin (or adminer), go to "SQL" and type the command:

RENAME TABLE current name_to new_name

Click on Run. After that, give F4 to update the list of tables in the database!