Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question

migrated from serverfault.com Jun 7 '11 at 21:35

This question came from our site for professional system and network administrators.

2 Answers 2

up vote 14 down vote accepted

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';
share|improve this answer

show table status like 'table1';

share|improve this answer
    
Which limits the rows but not the columns, right? –  Jack Douglas Mar 27 '12 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 '12 at 13:46

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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