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 ;
Bookmark and Share

Related posts:

  1. Stored procedure – Execute query if table or Column exists Well procedures mainly carried out working with information schema and...
  2. Stored procedure to Find database objects This procedure lists available database objects under passed database name....
  3. Ideas for select all columns but one mysql stored procedure Assume we’ve a table with 100 rows and we need...
  4. Search through all databases, tables, columns in mysql What will you do if one day some one ask...
  5. MySQL Stored procedure – Split Delimited string into Rows This procedure will split  a “;” separated column in to...


About this entry

You’re currently reading “Stored procedure to add-remove prefix by rename table mysql”, an entry on ..::CHANGE is INEVITABLE::..

Published:
02.08.10 / 2pm
Category:
technical
Tags:
, ,
Post Navigation:
«
»

I'm Looking For: