CODEWITHSUNDEEP
mysql
ursitesion
ursitesion

Reputation: 988

customize result of show table status from dbname

Is it possible to get the custom result when executing below query:

show table status from dbname

I customized "show processlist" query in this way:

SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST where time > 4 order by TIME desc;

In the same way, I want to get the custom result from above query.

Many thanks for your suggestions...

Upvotes: 0

Views: 306

Answers (1)

ursitesion
ursitesion

Reputation: 988

After going through information_schema, I got my answer which I want to share here.

SELECT * FROM information_schema.tables WHERE table_schema = 'dbname';

If want to list down only some specific columns, we can mention the column names separated by comma just after SELECT key. Also, we can add filter records by adding conditions in WHERE clause. For example: 

SELECT table_name,table_type,Engine,version,table_rows FROM information_schema.tables WHERE table_schema = 'jprod';

There are only two differences between below queries:

(a)show table status from dbname;
(b)SELECT * FROM information_schema.tables WHERE table_schema = 'dbname';
Query (b) provides 4 extra columns - (i) Table_catalog (ii) Table_schema (iii) Table_type (iv)Checksum
Some column names in query (a) is brief likewise table_name as name, Table_rows as rows, table_comment as comment.

Upvotes: 1

Related Questions