2qftxj5Z

1START TRANSACTION;
2
3DROP PROCEDURE IF EXISTS rename_tables;
4
5delimiter //
6CREATE PROCEDURE rename_tables(IN db CHAR(255))
7BEGIN
8  DECLARE done INT DEFAULT 0;
9  DECLARE from_table CHAR(255);
10  DECLARE cur1 CURSOR FOR SELECT TABLE_NAME FROM information_schema.TABLES WHERE TABLE_SCHEMA=db;
11  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
12
13  OPEN cur1;
14    
15  SET @rename_query = '';
16
17  read_loop: LOOP
18    IF done THEN
19      LEAVE read_loop;
20    END IF;
21
22    FETCH cur1 INTO from_table;
23    SET @to_table = CONCAT('_', from_table);
24
25    IF from_table != @to_table THEN
26      SET @old_query = @rename_query;
27      SET @rename_query = CONCAT(@old_query, 'RENAME TABLE `', db, '`.`', from_table, '` TO `', db, '`.`', @to_table, '`;\n');
28    END IF;
29
30  END LOOP;
31
32  PREPARE stmt FROM @rename_query;
33  EXECUTE stmt;
34  DEALLOCATE PREPARE stmt;
35      
36  CLOSE cur1;
37END//
38delimiter ;
39
40CALL rename_tables('mydb');
41
42DROP PROCEDURE IF EXISTS rename_tables;
43
44COMMIT;