 drop procedure if exists sp_TableEng;
create procedure sp_TableEng(dbname varchar(50))
BEGIN
DECLARE done INT DEFAULT 0;
DECLARE v_tbname varchar(500) default '';
DECLARE v_schema varchar(500) default '';
-- 顯示總記錄數
DECLARE curPos CURSOR FOR select table_schema,table_name from information_schema.tables where table_schema in(dbname) and engine='InnoDB';
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1;
select count(*) from information_schema.tables where table_schema in(dbname) and engine='InnoDB';
OPEN curPos;
REPEAT
FETCH curPos INTO v_schema,v_tbname;
if not done then
set @sql_delete =concat('alter table ',v_schema,'.',v_tbname, ' engine=''MyISAM''');
prepare sql_del from @sql_delete;
execute sql_del;
end if;
UNTIL done END REPEAT;
close curPos;
end;
|