ENSRdnhx

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 AND TABLE_NAME NOT LIKE "$%";
11  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
12
13  OPEN cur1;
14    
15  read_loop: LOOP
16    IF done THEN
17      LEAVE read_loop;
18    END IF;
19
20    FETCH cur1 INTO from_table;
21    SET @to_table = CONCAT('$', from_table);
22
23    IF from_table != @to_table THEN
24      SET @rename_query = CONCAT('RENAME TABLE `', db, '`.`', from_table, '` TO `', @to_table, '`;\n');
25      
26      PREPARE stmt FROM @rename_query;
27      EXECUTE stmt;
28      DEALLOCATE PREPARE stmt;
29    END IF;
30
31  END LOOP;
32
33  CLOSE cur1;
34END//
35delimiter ;
36
37CALL rename_tables('mydb');
38
39DROP PROCEDURE IF EXISTS rename_tables;
40
41COMMIT;