My (SQL) WorkLog: MySQL: Copy tables (Only structure and NO DATA) using stored procedure

Thursday, January 14, 2010

MySQL: Copy tables (Only structure and NO DATA) using stored procedure

Last week some one asked how to Copy the tables (Only structure and NO DATA..also no other DB objects) from one schema to another on EE. This can be easily done from command line but user wanted to do this thru stored procedure.
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(64),targetSchema VARCHAR(64))


BEGIN

DECLARE no_more_rows BOOLEAN;
DECLARE loop_cntr INT DEFAULT 0;
DECLARE num_rows INT DEFAULT 0;
DECLARE my_table VARCHAR(64);

DECLARE my_cur CURSOR FOR
SELECT TABLE_NAME AS myTable
 FROM information_schema.TABLES
WHERE information_schema.TABLES.TABLE_SCHEMA=sourceSchema AND TABLE_TYPE = 'BASE TABLE';


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');


3 comments:

rpbouman said...

Hi!

nice one. A few suggestions:

- your parameters are varchar(50). But table names and schema names can be 64 characters long.
- your query on information_schema.TABLES should have a WHERE TABLE_TYPE = 'BASE TABLE'. Otherwise, you will get in trouble in case you have views in the schema.

Umesh Shastry said...

Hi Roland,

Thanks for the suggestions. I'll incorporate the changes.

Regards,
Umesh

Guru said...

Thank you

Post a Comment