Skip navigation links

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..

DELIMITER $$

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

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;

repeat

fetch cur1 into v_table_name;



IF NOT done THEN



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



PREPARE s1 FROM @sql_text;

EXECUTE s1;





end if;



until done end repeat;



close cur1;



set foreign_key_checks='on';

END

Options: ReplyQuote


Subject Views Written By Posted
renaming a mysql database 1262 satya dixit 05/04/2012 05:59AM
Re: renaming a mysql database 1084 Scott Nemes 05/04/2012 08:56AM
Re: renaming a mysql database 646 Rick James 05/05/2012 01:38PM
Re: renaming a mysql database 567 satya dixit 05/05/2012 08:46PM
Re: renaming a mysql database 602 Trey Raymond 05/14/2012 12:11PM
Re: renaming a mysql database 602 satya dixit 05/05/2012 08:59PM
Re: renaming a mysql database 509 Rick James 05/06/2012 10:28PM
Re: renaming a mysql database 705 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.