MySQL Forums :: InnoDB :: renaming a mysql database

Advanced Search

Re: renaming a mysql database
Posted by: satya dixit ()
Date: May 07, 2012 04:09AM

Thanks Rick, this worked for me though there are some issues with triggered tables and tables with cascaded constraints..


CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_temp_test_rename_db`(db_to_rename varchar(100), rename_db_to varchar(100))

declare v_table_name varchar(100);

declare done boolean default false;

declare cur1 cursor for select table_name from information_schema.tables where table_schema=db_to_rename;

declare continue handler for not found set done=true;

set foreign_key_checks='off';

open cur1;


fetch cur1 into v_table_name;


SET @sql_text =concat("RENAME TABLE ",db_to_rename,".",v_table_name," TO ",rename_db_to,".",v_table_name,";");

PREPARE s1 FROM @sql_text;


end if;

until done end repeat;

close cur1;

set foreign_key_checks='on';


Options: ReplyQuote

Subject Views Written By Posted
renaming a mysql database 1816 satya dixit 05/04/2012 05:59AM
Re: renaming a mysql database 1347 Scott Nemes 05/04/2012 08:56AM
Re: renaming a mysql database 872 Rick James 05/05/2012 01:38PM
Re: renaming a mysql database 820 satya dixit 05/05/2012 08:46PM
Re: renaming a mysql database 813 Trey Raymond 05/14/2012 12:11PM
Re: renaming a mysql database 866 satya dixit 05/05/2012 08:59PM
Re: renaming a mysql database 745 Rick James 05/06/2012 10:28PM
Re: renaming a mysql database 1024 satya dixit 05/07/2012 04:09AM

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.