CODEWITHSUNDEEP
mysqlsql
screechOwl
screechOwl

Reputation: 28169

MySQL drop tables where engine is memory

I have a data base where I have permanent versions of tables that I make copies of and then convert to memory engine for faster performance. Is there a way to delete all the tables in a db by engine type? Something along the lines of:

drop * from db1 where engine = memory;

Any suggestions?

Upvotes: 2

Views: 1222

Answers (1)

Maxim Krizhanovsky
Maxim Krizhanovsky

Reputation: 26739

You can get list of table names with the following query:

select table_name 
from information_schema.tables 
where table_schema = 'db1' and engine = 'memory';

And then generate a query to drop them. As far as I know you cannot use expression / subquery for a tablename in the drop statement, so you will have to use 2 queries or create a stored procedure for this.

Upvotes: 3

Related Questions