Home > Computing > mysql ‘show table status’ via select

mysql ‘show table status’ via select

Just squirrelling this one away. The ‘show table status’ command is pretty cool, but sometimes I want to limit the amount of columns that are returned, or do something else like create ALTER TABLE commands to change the storage engine.

Just realized that the same information in ‘show table status’ is in the information_schema.tables table. So for example

select table_name, engine \
from information_schema.tables where table_schema = 'confluence';

Shows me all the tables and storage engines in the ‘confluence’ database.

And to create statements to alter the storage engine –

select concat('alter table ', table_name, ' engine = InnoDB;') from \
information_schema.tables where table_schema = 'confluence';

Also handy when creating commands like the above are the -B and -N options to the mysql command, which change the column separator to a tab (instead of pipe symbol) and turn off  column names.

mysql -B -N -u user -p -e \
  "select concat('alter table ', table_name, ' engine = InnoDB;') \
  from information_schema.tables where table_schema = 'confluence'" \
  > alter-tables.sql
Advertisements
Categories: Computing
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: