DROP PROCEDURE IF EXISTS AlterEngine;
DELIMITER |
CREATE PROCEDURE AlterEngine( pDb varchar(255), pTbl varchar(255),pEngine varchar(255))
COMMENT 'from the original by David Yeung'
BEGIN
DECLARE ctr INT DEFAULT 0;
DECLARE i INT DEFAULT 0;
IF LENGTH(pTbl)=0 THEN
BEGIN
SELECT COUNT(1) FROM information_schema.tables
WHERE table_schema = pDb AND engine != pEngine INTO ctr;
WHILE i < ctr DO
SET @sql = CONCAT( 'SELECT table_name FROM information_schema.tables',
' WHERE table_schema =', char(39), pDb, char(39),
' AND engine != ', char(39), pEngine, char(39),
' ORDER BY table_name DESC LIMIT ', i, ',1' );
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
SET @sql = CONCAT('ALTER TABLE ',pDb,'.',@tbname,' ENGINE=',pEngine);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
SET i = i + 1;
END WHILE;
END;
ELSE
BEGIN
SET @sql = CONCAT( 'ALTER TABLE ', pDb, '.', pTbl, ' ENGINE=', pEngine );
PREPARE stmt from @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
SELECT CONCAT( pDb, '.', pTbl, ' engine changed.' );
END;
END IF;
END |
DELIMITER ;
|
|