Determining Engine across multiple databaes

[gharabed]

I have multiple servers with multiple databases on each server. Before putting my physical backup strategy together I need to know which engine is being used for each table in each database on each server in order to be sure the backups are being done properly. What would be the easiest way to get a listing of each table in each database with the engine being used for each?

I've started to do this manually and it is a bit of a pain as I am having to go through each db and do a "show create table" for each table. This seems to be a bit of a pain. Is there some sort of script or query I can run against each db (I should say this is mySQL 4.1) to give me a listing of each table and the engine being used for each?

Thanks!

 

[r937]

SHOW TABLE STATUS FROM dbname

returns several columns, the one you want is Engine

r937.com | rudy.ca

 

[gharabed]

Right, but I don't wan't all of the other columns...just a listing of the tables and the engine. I think I found the answer to my own question...I am using the following OS command:

mysql -u root <dbname> -e 'show table status;' | awk '{ print $1 " - " $2;}'

This lists the table name, a ' - ', and the engine being used.

 

[r937]

ooh, cool -- what is that, perl?


r937.com | rudy.ca