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.
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 ;
I need a store procedure where if in one table an entry is abc123 | xyz123 and other table entry is lmn456 | rst456 then i want a third table which should map abc123 | lmn456 and xyz | rst456. I want a sql script as it runs faster than a program from java or etc. Can you help.
Stupid wordpress is breaking all the single and double quotes by changing them to the typographically-fancy versions. OP, you might put that stored procedure into a -pre- or -code- wrapper.
Thanks for suggestion; I will make that change.
Mean while you can also download the code from: “Download the Procedure: prefix_all”
really good example