Month: July 2014

Ever had an experience where you needed to rename a handful of table name into upper case. Below is a procedure to rename all tables into upper case in the current MySQL database/schema.

DELIMITER $$
 
DROP PROCEDURE IF EXISTS `PROC_TABLE_NAME_TO_UPPER_CASE`$$
 
CREATE PROCEDURE `PROC_TABLE_NAME_TO_UPPER_CASE`()
BEGIN
 DECLARE tbname TEXT DEFAULT ;
 DECLARE no_res INT DEFAULT 0;
 DECLARE cur CURSOR FOR
 SELECT t.table_name
 FROM information_schema.tables t
 WHERE t.table_schema = DATABASE() AND t.table_type=BASE TABLE;
 DECLARE CONTINUE HANDLER FOR NOT FOUND SET no_res = 1;
 
 OPEN cur;
 
 rnloop: LOOP
 FETCH cur INTO tbname;
 
 IF no_res = 1 THEN
 LEAVE rnloop;
 END IF;
 
 SET @s = CONCAT(RENAME TABLE `, tbname , ` TO ` , UPPER(tbname), ` );
 PREPARE stmt FROM @s;
 EXECUTE stmt;
 END LOOP;
 
 CLOSE cur;
 
END$$
 
DELIMITER ;

Just run the script and call it using

CALL PROC_TABLE_NAME_TO_UPPER_CASE();

then it will rename the tables. Be reminded though that you need to specify the database to be used before connecting to MySQL or by using the use command.