The first challenge is to retrieve a list of the tables in the database. Once I have this list, it's as simple as looping through the items via a
CURSOR and executing the
RENAME TABLE command dynamically with the table name.
Here is the stored procedure that I was able to come up with and successfully execute. I even went the extra effort to accept parameters for the database name!
DELIMITER //
DROP PROCEDURE IF EXISTS `RenameDatabase` //
CREATE PROCEDURE `RenameDatabase`(
new_database VARCHAR(255)
)
BEGIN
DECLARE done INT DEFAULT FALSE;
DECLARE num_rows INT;
DECLARE i INT;
DECLARE the_name VARCHAR(255);
DECLARE ren VARCHAR(255);
DECLARE table_names CURSOR FOR SELECT DISTINCT table_name FROM information_schema.columns WHERE table_schema = DATABASE();
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
OPEN table_names;
read_loop: LOOP
FETCH table_names INTO the_name;
IF done THEN
LEAVE read_loop;
END IF;
SET @ren = concat("RENAME TABLE `", DATABASE(), "`.", the_name, " TO `", new_database, "`.", the_name);
PREPARE ren FROM @ren;
EXECUTE ren;
END LOOP;
CLOSE table_names;
END //
DELIMITER ;
Next, you simply need to call the stored procedure. Before executing the stored procedure, simply make sure that the new database has been created and execute as follows:
call RenameDatabase('NEW_DATABASE_NAME');
Note: Being sure to replace NEW_DATABASE_NAME with the actual name of the database.
Published on Sep 28, 2012
Tags: SQL Tutorials for Beginners, Intermediate and Advanced Users
| rename database
| stored procedure
Unfortunately – and probably for good reason – the RENAME DATABASE command had a short life in Mysql for safety precaution reasons.
