08 Feb

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 ;

3 thoughts on “Stored procedure to add-remove prefix by rename table mysql

    • Arthur,

      Thanks for suggestion; I will make that change.
      Mean while you can also download the code from: “Download the Procedure: prefix_all”

      Thanks,
      Kedar.

Leave a Reply

Your email address will not be published. Required fields are marked *


*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

-- Kedar Vaijanapurkar --