http://www.experts-exchange.com/Database/MySQL/Q_25024073.html
So I came with a very small MySQL procedure which was doing as needed by the user. I'm not sure whether this is the best way to do this but "There is always room for improvement."
DELIMITER $$
DROP PROCEDURE IF EXISTS `CopySchema`$$
CREATE PROCEDURE `CopySchema`(sourceSchema VARCHAR(50),targetSchema VARCHAR(50))
BEGIN
DECLARE no_more_rows BOOLEAN;
DECLARE loop_cntr INT DEFAULT 0;
DECLARE num_rows INT DEFAULT 0;
DECLARE my_table VARCHAR(100);
DECLARE my_cur CURSOR FOR
SELECT TABLE_NAME AS myTable
FROM information_schema.TABLES
WHERE information_schema.TABLES.TABLE_SCHEMA=sourceSchema;
DECLARE CONTINUE HANDLER FOR NOT FOUND
SET no_more_rows = TRUE;
SET @tmp_sql= CONCAT("CREATE DATABASE IF NOT EXISTS ",targetSchema);
PREPARE s1 FROM @tmp_sql;
EXECUTE s1;
DEALLOCATE PREPARE s1;
OPEN my_cur;
select FOUND_ROWS() into num_rows;
the_loop: LOOP
FETCH my_cur
INTO my_table;
IF no_more_rows THEN
CLOSE my_cur;
LEAVE the_loop;
END IF;
SET @tmp_sql= CONCAT("CREATE TABLE IF NOT EXISTS ",targetSchema,'.',my_table,' LIKE ',sourceSchema,'.',my_table);
PREPARE s1 FROM @tmp_sql;
EXECUTE s1;
DEALLOCATE PREPARE s1;
SET loop_cntr = loop_cntr + 1;
END LOOP the_loop;
SELECT CONCAT('Summary: ', loop_cntr, ' tables copied from schema "',sourceSchema,'" to "',targetSchema,'"') AS "Schema copying";
END$$
DELIMITER ;
Usage:
call CopySchema('dba','dba_dummy');
PlanetMySQL Voting: Vote UP / Vote DOWN