LinuxQuestions.org
Review your favorite Linux distribution.
Go Back   LinuxQuestions.org > Forums > Non-*NIX Forums > Programming
User Name
Password
Programming This forum is for all programming questions.
The question does not have to be directly related to Linux and any language is fair game.

Notices


Reply
  Search this Thread
Old 09-13-2019, 07:09 PM   #1
bmxakias
Member
 
Registered: Jan 2016
Posts: 164

Rep: Reputation: Disabled
Question How to list all databases that have inside tables with MyIsam engine?


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
 
Old 09-13-2019, 10:42 PM   #2
NevemTeve
Senior Member
 
Registered: Oct 2011
Location: Budapest
Distribution: Debian/GNU/Linux, AIX
Posts: 3,860

Rep: Reputation: 1344Reputation: 1344Reputation: 1344Reputation: 1344Reputation: 1344Reputation: 1344Reputation: 1344Reputation: 1344Reputation: 1344Reputation: 1344
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.

Last edited by NevemTeve; 09-13-2019 at 11:01 PM.
 
1 members found this post helpful.
Old 09-13-2019, 10:57 PM   #3
bmxakias
Member
 
Registered: Jan 2016
Posts: 164

Original Poster
Rep: Reputation: Disabled
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...

Last edited by bmxakias; 09-13-2019 at 10:58 PM.
 
Old 09-13-2019, 11:09 PM   #4
NevemTeve
Senior Member
 
Registered: Oct 2011
Location: Budapest
Distribution: Debian/GNU/Linux, AIX
Posts: 3,860

Rep: Reputation: 1344Reputation: 1344Reputation: 1344Reputation: 1344Reputation: 1344Reputation: 1344Reputation: 1344Reputation: 1344Reputation: 1344Reputation: 1344
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;

Last edited by NevemTeve; 09-13-2019 at 11:11 PM.
 
2 members found this post helpful.
Old 09-14-2019, 08:23 AM   #5
bmxakias
Member
 
Registered: Jan 2016
Posts: 164

Original Poster
Rep: Reputation: Disabled
I thought the same but i was getting always a syntax error

It works great thank you !!!
 
  


Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off



Similar Threads
Thread Thread Starter Forum Replies Last Post
LXer: List PostgreSQL Databases and Tables using psql LXer Syndicated Linux News 0 07-02-2019 10:42 AM
LXer: MySQL Incremental Backup - Point In Time Backup and Recovery of InnoDB and MyIsam Databases LXer Syndicated Linux News 0 05-27-2015 05:24 PM
How to convert/changes MySQL Tables from MyISAM to NDBCLUSTER cparapat Linux - Software 3 02-04-2011 03:05 AM
mysql MyISAM table doesn't have .MYD and MYI files. celeron Linux - Server 2 02-01-2011 07:35 AM
Overhead tables MyISAM in MySQL Stephan_Craft Linux - Software 2 07-20-2008 04:38 PM

LinuxQuestions.org > Forums > Non-*NIX Forums > Programming

All times are GMT -5. The time now is 11:42 PM.

Main Menu
Advertisement
My LQ
Write for LQ
LinuxQuestions.org is looking for people interested in writing Editorials, Articles, Reviews, and more. If you'd like to contribute content, let us know.
Main Menu
Syndicate
RSS1  Latest Threads
RSS1  LQ News
Twitter: @linuxquestions
Facebook: linuxquestions Google+: linuxquestions
Open Source Consulting | Domain Registration