Renaming a Database in Mysql Renaming a Database in Mysql

Unfortunately – and probably for good reason – the RENAME DATABASE command had a short life in Mysql for safety precaution reasons.

After doing some research, it appears that the best approach is as follows as it works well for both MyISAM and InnoDB table types:

  1. Create new database

  2. Perform RENAME TABLE command that includes the table and database from the old one to the newly created database

  3. Repeat once for each table

In all of the examples I didn't find an automated script – time to make one I thought!


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

Related Posts

Did you enjoy this article? If you did here are some more articles that I thought you will enjoy as they are very similar to the article that you just finished reading.

Jamie Munro
Jamie Munro

Jamie Munro is the author of ASP.NET MVC 5 with Bootstrap and Knockout.js, Knockout.js: Building Dynamic Client-Side Web Applications, 20 Recipes for Programming MVC 3, and 20 Recipes for Programming PhoneGap. He has been developing websites and web applications for over 20 years.

Jamie began his writing career in 2009. As the success of Jamie's blog grew, he turned his writing passion to books about web development in hopes that his many years of experience could be passed on to his readers.

Tutorials

Learn how to code in HTML, CSS, JavaScript, Python, Ruby, PHP, Java, C#, SQL, and more.

No matter the programming language you're looking to learn, I've hopefully compiled an incredible set of tutorials for you to learn; whether you are beginner or an expert, there is something for everyone to learn. Each topic I go in-depth and provide many examples throughout. I can't wait for you to dig in and improve your skillset with any of the tutorials below.

Front-End

Server-Side

Database