In this article I will show you how to change the table engine of all tables in database. We can change one by one table manually but this is not a way on place where we have large number of tables in database.
For example we have to change Engine from "Innodb" to "MyISAM" .
Using Information schema table , I will run a query as shown below and generate Script . After that copy the result set as Script in Script Tab and run all at a time.
SELECT CONCAT('ALTER TABLE `',table_schema,'.', table_name, '` ENGINE=MyISAM ;') AS sql_statements
FROM information_schema.tables AS tb
WHERE table_schema ='inter15'
AND `ENGINE` = 'innodb '
AND `TABLE_TYPE` = 'BASE TABLE'
ORDER BY table_name DESC;
Note: Please take the necessary backup before any changes.
No comments:
Post a Comment