UYdjBc4W

1DELIMITER $$
2DROP PROCEDURE IF EXISTS `CopyDB` $$
3CREATE PROCEDURE `CopyDB` (sourceDB VARCHAR(64),targetDB VARCHAR(64))
4TheStoredProcedure:BEGIN
5
6    DECLARE found_count,ndx,ndx_last INT;
7    DECLARE sqlcmd VARCHAR(1024);
8
9    SELECT COUNT(1) INTO found_count
10    FROM information_schema.tables
11    WHERE table_schema = sourceDB;
12    IF found_count = 0 THEN
13        LEAVE TheStoredProcedure;
14    END IF;
15
16    DROP TABLE IF EXISTS DBTablesToCopy;
17    CREATE TABLE DBTablesToCopy
18    (
19        id INT NOT NULL AUTO_INCREMENT,
20        src  VARCHAR(64),
21        tgt  VARCHAR(64),
22        tbl  VARCHAR(64),
23        PRIMARY KEY (id)
24    );
25    DROP TABLE IF EXISTS SQLScriptToCopyTables;
26    CREATE TABLE SQLScriptToCopyTables
27    (
28        id INT NOT NULL AUTO_INCREMENT,
29        sqltext VARCHAR(1024),
30        PRIMARY KEY (id)
31    );
32
33    INSERT INTO DBTablesToCopy (src,tgt,tbl)
34    SELECT sourceDB,targetDB,table_name
35    FROM information_schema.tables
36    WHERE table_schema = sourceDB;
37
38    SELECT MAX(id) INTO ndx_last FROM DBTablesToCopy;
39    SET ndx = 0;
40    WHILE ndx < ndx_last DO
41        SET ndx = ndx + 1;
42        INSERT INTO SQLScriptToCopyTables (sqltext)
43        SELECT CONCAT('DROP TABLE IF EXISTS ',tgt,'.',tbl)
44        FROM DBTablesToCopy WHERE id = ndx;
45    END WHILE;
46    SET ndx = 0;
47    WHILE ndx < ndx_last DO
48        SET ndx = ndx + 1;
49        INSERT INTO SQLScriptToCopyTables (sqltext)
50        SELECT CONCAT('CREATE TABLE ',tgt,'.',tbl,' LIKE ',src,'.',tbl)
51        FROM DBTablesToCopy WHERE id = ndx;
52    END WHILE;
53    SET ndx = 0;
54    WHILE ndx < ndx_last DO
55        SET ndx = ndx + 1;
56        INSERT INTO SQLScriptToCopyTables (sqltext)
57        SELECT CONCAT('INSERT ',tgt,'.',tbl,' SELECT * FROM ',src,'.',tbl)
58        FROM DBTablesToCopy WHERE id = ndx;
59    END WHILE;
60
61    SELECT MAX(id) INTO ndx_last FROM SQLScriptToCopyTables;
62    SET ndx = 0;
63    WHILE ndx < ndx_last DO
64        SET ndx = ndx + 1;
65        SELECT sqltext INTO @stmt FROM SQLScriptToCopyTables WHERE id = ndx;
66        PREPARE s1 FROM @stmt;
67        EXECUTE s1;
68        DEALLOCATE PREPARE s1;
69    END WHILE;
70    
71    DROP TABLE IF EXISTS DBTablesToCopy;
72    DROP TABLE IF EXISTS SQLScriptToCopyTables;
73END $$
74DELIMITER ;