0

I've got a stored procedure that copies all tables from one database to another but steps through each table individually (because of queries).

I need to add to the stored procedure to copy all views from the original database but I'm not totally sure how to loop through the results of this query:

SHOW FULL TABLES IN stations WHERE TABLE_TYPE LIKE 'VIEW';

Stored procedure:

USE station;
DROP PROCEDURE IF EXISTS updateStation;
DELIMITER //
CREATE PROCEDURE updateStation (IN stationName VARCHAR(255))
 BEGIN
   # stationList
   DROP TABLE IF EXISTS station.stationList;
   CREATE TABLE station.stationList LIKE stations.stationList;
   INSERT INTO station.stationList SELECT s.* FROM stations.stationList s WHERE s.hostName = stationName;
   # pinTypes
   DROP TABLE IF EXISTS pinTypes;
   CREATE TABLE station.pinTypes LIKE stations.pinTypes;
   INSERT INTO station.pinTypes SELECT p.* FROM stations.pinTypes p;
   # pinModes
   DROP TABLE IF EXISTS pinModes;
   CREATE TABLE station.pinModes LIKE stations.pinModes;
   INSERT INTO station.pinModes SELECT p.* FROM stations.pinModes p;
    # Copy All Views
    FOR SELECT TABLES IN stations WHERE TABLE_TYPE LIKE 'VIEW'
    DROP TABLE IF EXISTS ?;
    CREATE TABLE station.? LIKE stations.?;
 END //
DELIMITER ;

Perhaps something like this? :

DECLARE cur CURSOR FOR SELECT TABLE_NAME, ENGINE FROM information_schema.TABLES where TABLE_SCHEMA = 'stations' AND ENGINE IS NULL;
DECLARE done INT DEFAULT 0;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
OPEN cur;
read_loop: LOOP
    FETCH cur INTO tableName;
        IF done THEN
          LEAVE read_loop;
        END IF;
    DROP TABLE IF EXISTS 'station'.tableName;
    CREATE TABLE 'station'.tableName LIKE 'stations'.tableName;
END LOOP;

CLOSE cur;

0

You need to call SHOW CREATE VIEW to get the view code. However, that will have the database name in the code. You would need to parse the output, replace that database name, then execute dynamic statements with PREPARE and EXECUTE.

Stored procedures may be the most difficult way to manage this. For example, you can mysqldump can dump/load the views from one database to another in one line : https://stackoverflow.com/questions/1658204/backing-up-views-with-mysql-dump

Other tools like Flyway and Liquibase can help with schema management as well.

  • It sounds hardcoding them into the stored procedure will be the easiest way to handle this then. Thank you for your input. – Warrior Aug 14 '18 at 23:36
  • I was able to do it really easily using phpMyAdmin. I selected the views from stations database, then selected all at the bottom of the result set, then from the drop down list, I picked Copy Table, choose database station, then selected Add DROP TABLE statement and everything was copied over. Unfortunately, it did not give me the code it used to complete this task like it does for almost everything else I do on there. – Warrior Aug 15 '18 at 1:04
  • Ahh, I see the problem now... The copied over table uses the EXACT same code as the stations database and references the stations tables. – Warrior Aug 15 '18 at 1:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.