#
#  requirements: MySQL 5
#  stored procedure: sp_copy_all_tables
#  parameter: source_database     - source database name
#  parameter: destination_database   - destination database name
#  description:  copy all tables from source database to destination database
#
 
DELIMITER \\
 
DROP PROCEDURE IF EXISTS `sp_copy_all_tables`\\
 

CREATE PROCEDURE sp_copy_all_tables(IN source_database VARCHAR(100),IN destination_database VARCHAR(100))

 
BEGIN
 
 DECLARE sql_query VARCHAR(250) DEFAULT "";
 DECLARE destination_table VARCHAR(250);
 DECLARE source_table VARCHAR(250);
 DECLARE exit_status INT DEFAULT 0;
 DECLARE tbl_name char(150);
 DECLARE list CURSOR FOR SELECT TABLE_NAME FROM information_schema.TABLES
  WHERE information_schema.TABLES.table_schema = source_database;
 DECLARE CONTINUE HANDLER FOR SQLSTATE  '02000' SET exit_status = 1;
 
 OPEN list;
 
 REPEAT

  FETCH list INTO tbl_name;
  IF exit_status = 0 THEN

     SET destination_table = CONCAT(destination_database,".",tbl_name);
     SET source_table = CONCAT(source_database,".",tbl_name);
     SET @sql_query = CONCAT("CREATE TABLE ",destination_table," SELECT * FROM ",source_table);
 
     PREPARE sql_stm FROM @sql_query;
     EXECUTE sql_stm;
     DEALLOCATE PREPARE sql_stm;
  END IF;
 UNTIL exit_status = 1 END REPEAT;
 
 CLOSE list;
 

END\\
 
DELIMITER ;