Summary of yer tables
Sometimes, you just want to see what’s going on from a high level. “HOW CAN I GET A SUMMARY OF MY TABLES?!” you may have asked yourself…
To do this, you’ll need to do a simple query into the TABLES table in the information_schema database. Like this:
mysql> use information_schema;
Database changed
mysql>
It’s a habit for me, but I always like to describe the table so I know what I’m looking at:
mysql> desc TABLES;
+-----------------+---------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------------+---------------------+------+-----+---------+-------+
| TABLE_CATALOG | varchar(512) | NO | | | |
| TABLE_SCHEMA | varchar(64) | NO | | | |
| TABLE_NAME | varchar(64) | NO | | | |
| TABLE_TYPE | varchar(64) | NO | | | |
| ENGINE | varchar(64) | YES | | NULL | |
| VERSION | bigint(21) unsigned | YES | | NULL | |
| ROW_FORMAT | varchar(10) | YES | | NULL | |
| TABLE_ROWS | bigint(21) unsigned | YES | | NULL | |
| AVG_ROW_LENGTH | bigint(21) unsigned | YES | | NULL | |
| DATA_LENGTH | bigint(21) unsigned | YES | | NULL | |
| MAX_DATA_LENGTH | bigint(21) unsigned | YES | | NULL | |
| INDEX_LENGTH | bigint(21) unsigned | YES | | NULL | |
| DATA_FREE | bigint(21) unsigned | YES | | NULL | |
| AUTO_INCREMENT | bigint(21) unsigned | YES | | NULL | |
| CREATE_TIME | datetime | YES | | NULL | |
| UPDATE_TIME | datetime | YES | | NULL | |
| CHECK_TIME | datetime | YES | | NULL | |
| TABLE_COLLATION | varchar(32) | YES | | NULL | |
| CHECKSUM | bigint(21) unsigned | YES | | NULL | |
| CREATE_OPTIONS | varchar(255) | YES | | NULL | |
| TABLE_COMMENT | varchar(2048) | NO | | | |
+-----------------+---------------------+------+-----+---------+-------+
21 rows in set (0.00 sec)
mysql>
Now, we can see what we’re looking for. Here’s what I generally like to do:
mysql> select TABLE_NAME, TABLE_ROWS, AVG_ROW_LENGTH, DATA_LENGTH, MAX_DATA_LENGTH, INDEX_LENGTH, AUTO_INCREMENT from TABLES where TABLE_SCHEMA='werd';
+-----------------------+------------+----------------+-------------+------------------+--------------+----------------+
| TABLE_NAME | TABLE_ROWS | AVG_ROW_LENGTH | DATA_LENGTH | MAX_DATA_LENGTH | INDEX_LENGTH | AUTO_INCREMENT |
+-----------------------+------------+----------------+-------------+------------------+--------------+----------------+
| wp_commentmeta | 0 | 0 | 0 | 281474976710655 | 4096 | 1 |
| wp_comments | 1 | 228 | 228 | 281474976710655 | 7168 | 2 |
| wp_hook_list | 0 | 0 | 16384 | 0 | 0 | NULL |
| wp_links | 0 | 0 | 0 | 281474976710655 | 1024 | 1 |
| wp_options | 154 | 2548 | 430244 | 281474976710655 | 13312 | 835 |
| wp_postmeta | 9 | 44 | 404 | 281474976710655 | 10240 | 20 |
| wp_posts | 8 | 298 | 2388 | 281474976710655 | 11264 | 17 |
| wp_term_relationships | 3 | 21 | 63 | 5910974510923775 | 3072 | NULL |
| wp_term_taxonomy | 1 | 40 | 40 | 281474976710655 | 4096 | 2 |
| wp_terms | 1 | 40 | 40 | 281474976710655 | 11264 | 2 |
| wp_usermeta | 19 | 61 | 1160 | 281474976710655 | 10240 | 20 |
| wp_users | 1 | 92 | 92 | 281474976710655 | 4096 | 2 |
+-----------------------+------------+----------------+-------------+------------------+--------------+----------------+
12 rows in set (0.00 sec)
mysql>
Yeah, even my schema names are trendy and clever.</sarcasm>
Anyways, now you can sort and/or select however you want! Grok yer data, amigo. =]
I realize my code blocks are not blocking well enough. Will fix.