Follow us on Social Media:

MySQL Database Administrator

Boost Your Quick Knowledge in MySQL DBA Work, Concepts, Tips and Trick

Thursday, 19 April 2018

How to Change MySQL table Engine of all Tables in Database ?

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:

Please Like and Subscribe us ....

Please visit the website on daily basis , Your New articles will publish at any moment. Please Subscribe us for getting Latest Updates...