How to SELECT from SHOW TABLE STATUS results

Question

I'd like to limit the rows and columns that come back from the

SHOW TABLE STATUS

command in MySQL 5.1. Is there a way to get this same information through a SELECT statement so I can manipulate the results in a normal way?

Answer 1

This has more columns than SHOW TABLE STATUS; but does the trick:

SELECT * FROM information_schema.tables WHERE table_schema = DATABASE();

UPDATE 2011-06-07 19:02

SELECT table_name,Engine,Version,Row_format,table_rows,Avg_row_length,
Data_length,Max_data_length,Index_length,Data_free,Auto_increment,
Create_time,Update_time,Check_time,table_collation,Checksum,
Create_options,table_comment FROM information_schema.tables
WHERE table_schema = DATABASE();

These queries work if you set the current database.

You can also hard code the specific database:

SELECT table_name,Engine,Version,Row_format,table_rows,Avg_row_length,
Data_length,Max_data_length,Index_length,Data_free,Auto_increment,
Create_time,Update_time,Check_time,table_collation,Checksum,
Create_options,table_comment FROM information_schema.tables
WHERE table_schema = 'mysql';

Answer 2

show table status like 'table1';