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';

You can't manipulate the columns this way, but it is easier to select just the tables you want and get the normal SHOW TABLE STATUS output.

Answer 3

You can use WHERE or LIKE as for SELECT:

show table status where name='name'; 

Answer 4

show table status can be used without selecting current database:

show table status from DbName where name='TableName';

See documentation:

SHOW TABLE STATUS
    [{FROM | IN} db_name]
    [LIKE 'pattern' | WHERE expr]

Answer 5

You can use this query to display all Information Schema.

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

or the shorthand:

SHOW TABLE STATUS LIKE 'tableName'