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:

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 ;

Stored procedure – Execute query if table or Column exists Well procedures mainly carried out working with information schema and...
Stored procedure to Find database objects This procedure lists available database objects under passed database name....
MySQL Stored procedure to Generate-Extract Insert Statement A lot of places I saw people asking for ways...

Post Published: 08 February 2010
Author: Kedar
Found in section: Code, MySQL, Technical

Tags: MySQL, stored procedure, Technical

Previous Topic: How music can rescue-Help for Haiti-Download to Donate campaign

Next Topic: Whats New-Google Buzz in Gmail Inbox, Mobile

Reader Feedback