MySQL Forums
Forum List  »  InnoDB

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
1841
May 04, 2012 05:59AM
1358
May 04, 2012 08:56AM
879
May 05, 2012 01:38PM
827
May 05, 2012 08:46PM
822
May 14, 2012 12:11PM
873
May 05, 2012 08:59PM
752
May 06, 2012 10:28PM
Re: renaming a mysql database
1036
May 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.