Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1543
  • Last Modified:

Procedure to create multiple tables using other table definitions

I have a schema that has 'DB1' with several tables

I need someone to sketch out a procedure / script that will create a new schema using a user defined variable (@DBname) and then create empty tables in @DBname using the table definitions from DB1.

I want something automated where only @DBname needs to be specified.  Anyone know how to do that?

With Regards-
Sam
0
SAbboushi
Asked:
SAbboushi
  • 2
1 Solution
 
UmeshMySQL Principle Technical Support EngineerCommented:
Try this...thru SP... alternativelly this can be easily done from commandline..
DELIMITER $$ 
  
DROP PROCEDURE IF EXISTS uCCSchema$$ 
  
CREATE PROCEDURE uCCSchema(IN NewSchema VARCHAR(50),IN OldSchema VARCHAR(50)) 
BEGIN 


-- Declare variables used just for cursor and loop control
  DECLARE no_more_rows BOOLEAN;
  DECLARE loop_cntr INT DEFAULT 0;
  DECLARE num_rows INT DEFAULT 0;
  DECLARE my_table VARCHAR(100);

  -- Declare the cursor
  DECLARE friends_cur CURSOR FOR
	SELECT TABLE_NAME AS myTable
	  FROM information_schema.TABLES
	WHERE information_schema.TABLES.TABLE_SCHEMA=OldSchema;

  -- Declare 'handlers' for exceptions
  DECLARE CONTINUE HANDLER FOR NOT FOUND
    SET no_more_rows = TRUE;

        SET @tmp_sql= CONCAT("CREATE DATABASE ",NewSchema); 
        PREPARE s1 FROM @tmp_sql; 
        EXECUTE s1; 
        DEALLOCATE PREPARE s1; 

  /*
    Now the programming logic
  */

  -- 'open' the cursor and capture the number of rows returned
  -- (the 'select' gets invoked when the cursor is 'opened')
  OPEN friends_cur;
  select FOUND_ROWS() into num_rows;

  the_loop: LOOP

    FETCH  friends_cur
    INTO   my_table;

    -- break out of the loop if
      -- 1) there were no records, or
      -- 2) we've processed them all
    IF no_more_rows THEN
        CLOSE friends_cur;
        LEAVE the_loop;
    END IF;

    -- the equivalent of a 'print statement' in a stored procedure
    -- it simply displays output for each loop

        SET @tmp_sql= CONCAT("CREATE TABLE ",NewSchema,'.',my_table,' LIKE ',OldSchema,'.',my_table); 
        PREPARE s1 FROM @tmp_sql; 
        EXECUTE s1; 
        DEALLOCATE PREPARE s1; 


    -- count the number of times looped
    SET loop_cntr = loop_cntr + 1;

  END LOOP the_loop;



END$$ 
  
DELIMITER ;


### Usage..

call uCCSchema('NewDB','DB1');

Open in new window

0
 
SAbboushiAuthor Commented:
Nice - from what I can tell:

This creates a record in the mysql.proc table that will remain there until deleted?

How do I EDIT the stored procedure?  the body_utf8 field is empty... I thought it would contain the procedure code??
0
 
UmeshMySQL Principle Technical Support EngineerCommented:
Hi!!

updated version of stored procedure is avail on below url

http://ushastry.blogspot.com/2010/01/mysql-copy-tables-only-structure-and-no.html

Regards,
Umesh
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now