How to bulk rename table MySQL

Are you tired of manually renaming MySQL tables, one by one, especially when dealing with a large number of them? In this guide, we’ll introduce you to a MySQL stored procedure that simplifies the process of renaming tables, allowing you to effortlessly add or remove prefixes in bulk.

Overview

A friend of mine was renaming 100+ tables by using replace methods in notepad. I showed him a bit better editor, Editplus. With the support of regular expressions in Editplus we could parse his contents to prepare an SQL file containing RENAME TABLE commands.

Rename tables in MySQL is not very common administrative task, it can be time-consuming and error-prone when dealing with multiple tables. I wrote this blog to help people wanting to rename multiple MySQL tables or perform bulk rename operations in MySQL. I wrote a custom MySQL stored procedure that provides a practical solution by automating rename of a MySQL table to add or remove prefixes. Although the solution is case specific, it should help you. In case you need it to be tweaked for your need, leave me a comment.

Rename Table

1. Adding Prefixes: If you need to add a prefix to table names, this option has you covered. It can be particularly useful for organizing tables or distinguishing between different data sets.
2. Removing Prefixes: On the other hand, if you wish to clean up your table names by removing existing prefixes, this function will do the job efficiently.
   For example, clientA_table* to be renamed to clientB_table*

Download the Procedure to rename MySQL tables: prefix_all

Steps to utilize my MySQL stored procedure:

Step 1: Execute or Create the Stored Procedure

You can either download the SQL script containing the stored procedure or copy and paste it into your MySQL environment.

wget http://kedar.nitty-witty.com/wp-content/uploads/2010/02/prefix_all.sql_.txt
mysql test < prefix_all.sql_.txt

Step 2: Execute the Procedure

Call the stored procedure with the following parameters:

• in_db (VARCHAR): Specify the name of the target database.
• in_prefix (VARCHAR): Define the prefix you want to add or remove.
• in_add_rem (TINYINT): Use 1 to add a prefix or 0 to remove it.

Example:

CALL prefix_all('YOUR_DATABASE_NAME', 'PREFIX', 1);

Rename MySQL Tables in bulk

DELIMITER $

DROP PROCEDURE IF EXISTS `prefix_all` $
CREATE PROCEDURE `prefix_all` (in_db varchar(20),in_prefix varchar(10),in_add_rem TINYINT(1))
BEGIN

DECLARE done INT default 0;
DECLARE tbl_nm VARCHAR(30);
DECLARE ren VARCHAR(200);

DECLARE table_cur CURSOR FOR select table_name from information_schema.tables where table_schema=in_db;

DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1;
OPEN table_cur;
  rename_loop:LOOP
    FETCH table_cur INTO tbl_nm;
    IF done=1 THEN
      LEAVE rename_loop;
    END IF;
    if in_add_rem=1 then #ADD
      SET @ren = concat("rename table ", in_db,'.',tbl_nm ," to ",in_db,'.',in_prefix,tbl_nm,";");
    else
      set @ren= concat("rename table ", in_db,'.',tbl_nm ," to ",in_db,'.',right(tbl_nm,length(tbl_nm)-length(in_prefix)),';');
    end if;
#    select @ren;
    prepare ren from @ren;
    execute ren;
  END LOOP;
CLOSE table_cur;
select table_name 'Tables' from information_schema.tables where table_schema=in_db;

END $

DELIMITER ;

Conclusion

Simplify your MySQL table management tasks by using our custom stored procedure. Whether you need to add or remove prefixes from table names, this efficient solution will save you time and effort.