Stored procedure to add-remove prefix by rename table mysql

Stored procedure to add-remove prefix by rename table mysql

Here is one more procedure – (this time) for mass renaming of table. Adding and Removing table-name prefixes 

A friend of mine was renaming 100+ tables by using replace methods in notepad.

I showed em a bit better editor: Editplus and then I thought of rescue rest of those who are still interested in some techie-solution to such things.

This procedure is outcome of that try and its as usual very simple one with two functionalities:

• rename mysql table of a database by adding prefix
• rename mysql table of a database by removing prefix

So below mysql stored procedure just not renames to add but also removes prefixes from table names.

Download the Procedure: prefix_all

How to use:

1. Execute / Create Stored procedure by downloading or copy pasting sql script.

2. Execute: call prefix_all(‘DATABASE-NAME’,’PREFIX’,0);

Where 0 will remove the prefix from mysql table name, while 1 will add prefix.

Code:

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 ;