MySQL copy all views from one database to another in stored procedure

Question

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;

Answer 1

You can get information about the views from INFORMATION_SCHEMA.VIEWS

SELECT TABLE_SCHEMA, TABLE_NAME, VIEW_DEFINITION
FROM INFORMATION_SCHEMA.VIEWS

If you want to place the view in another schema (database in MySQL), replace TABLE_SCHEMA above.

Answer 2

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.