[SOLVED] How to list all databases that have inside tables with MyIsam engine?

bmxakias · 09-13-2019, 07:09 PM

Hello

I am trying to create a bash script that will list only the databases that have inside tables with MyIsam engine.

Most samples on the net require to add a specific database name or the output has also the tables names

Thank you

NevemTeve · 09-13-2019, 10:42 PM

Did you try to read meta-data from INFORMATION_SCHEMA.TABLES?
https://dev.mysql.com/doc/refman/8.0...les-table.html

Code:

SELECT DISTINCT table_schema FROM information_schema.tables WHERE engine='MyIsam';

Or something like that.

bmxakias · 09-13-2019, 10:57 PM

I did but i was not able to get the right syntax for listing all databases that have inside tables with MyIsam engine.

At the moment i tried:

Code:

select table_schema as database_name, table_name from information_schema.tables tab where engine = 'MyISAM' and table_type = 'BASE TABLE' and table_schema not in ('information_schema', 'sys', 'performance_schema','mysql') -- and table_schema = 'your database name' 
order by table_schema,
         table_name;

but i get always the table names also...

The target is to be able to check automatically without adding any database names all databases for tables using MyISAM engine and then list them...

NevemTeve · 09-13-2019, 11:09 PM

Then leave out TABLE_NAME:

Code:

select table_schema as database_name
from information_schema.tables tab
where engine = 'MyISAM' and table_type = 'BASE TABLE' and
  table_schema not in ('information_schema', 'sys', 'performance_schema','mysql')
group by table_schema
order by table_schema;

bmxakias · 09-14-2019, 08:23 AM

I thought the same but i was getting always a syntax error

It works great thank you !!!