Monday, November 21, 2011

find tables of a particular storage engine in mysql


If you have a database with some tables using MyISAM and some using InnoDB, you probably want to find out which tables are using InnoDB. 



SELECT TABLE_NAME FROM information_schema.TABLES WHERE TABLE_SCHEMA = 'database_name' AND ENGINE = 'InnoDB';



It is also easy to find out a specific table's storage engine:



SELECT ENGINE FROM information_schema.TABLES WHERE TABLE_SCHEMA = 'database_name' AND TABLE_NAME= 'table_name';