Re: renaming a mysql database

[satya dixit]

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