-- Collection of code snippets by Arne Vajhøj 
-- (likely posted to comp.os.vms/INFO-VAX, INFO-TPU, MACRO32, eksperten.dk, newz.dk, LinkedIn or other place sometime between 1990 and now) 
DELIMITER //
CREATE PROCEDURE droptables(_prefix VARCHAR(10), _db VARCHAR(255))
BEGIN
    DECLARE done INTEGER;
    DECLARE tblnam VARCHAR(255);
    DECLARE cur CURSOR FOR SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA=_db AND TABLE_TYPE='Base table' AND TABLE_NAME LIKE CONCAT(_prefix, '%');
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
    OPEN cur;
    SET done = 0;
    REPEAT
        FETCH cur INTO tblnam;
        IF NOT done THEN
            -- SET @sql = CONCAT('DROP TABLE ', tblnam);
            SET @sql = CONCAT('SELECT * FROM ', tblnam);
            PREPARE stmt FROM @sql;
            EXECUTE stmt;
            DEALLOCATE PREPARE stmt;
        END IF;
    UNTIL done END REPEAT;
    CLOSE cur;
END//
DELIMITER ;

CALL droptables('T', 'Test');

DROP PROCEDURE droptables;
