#
#  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 ;