43

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?

5 Answers 5

41

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

2
  • Which limits the rows but not the columns, right? Mar 27, 2012 at 6:48
  • Correct. 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.
    – John L
    Mar 27, 2012 at 13:46
3

You can use WHERE or LIKE as for SELECT:

show table status where name='name'; 
2

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]
-2

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'

1
  • 1
    I don't see anything in this answer that hasn't been already posted in other answers. Mar 10, 2018 at 17:01

Not the answer you're looking for? Browse other questions tagged or ask your own question.