0

is there a mysql command that will show me the tables and how many rows they have in them?

closed as off topic by slm, jasonwryan, Anthon, Mat, Stéphane Gimenez Jun 11 '13 at 11:01

Questions on Unix & Linux Stack Exchange are expected to relate to Unix or Linux within the scope defined by the community. Consider editing the question or leaving comments for improvement if you believe the question can be reworded to fit within the scope. Read more about reopening questions here. If this question can be reworded to fit the rules in the help center, please edit the question.

  • 1
    obviously this was a valid question with 2500 views! dont see why it has to be closed just because a few thought it didn't pertain to them. – Patoshi パトシ Sep 24 '14 at 14:56
3

Starting in MySQL 5 you can query the virtual table information_schema which contains metadata about the tables within your MySQL database.

To find out the number of rows for every table across every database:

$ mysql -u root -p \
    -e "select table_schema,table_name,table_rows from information_schema.tables;"
+---------------------+---------------------------------------+------------+
| table_schema        | table_name                            | table_rows |
+---------------------+---------------------------------------+------------+
| information_schema  | CHARACTER_SETS                        |       NULL | 
| information_schema  | COLLATIONS                            |       NULL | 
| information_schema  | COLLATION_CHARACTER_SET_APPLICABILITY |       NULL | 
...
...
| arrdb01             | active_part                           |         24 | 
| arrdb01             | audit_record                          |         19 | 
| arrdb01             | code                                  |          8 | 
| arrdb01             | part_obj                              |          0 | 
| arrdb02             | active_part                           |         24 | 
| arrdb02             | audit_record                          |         14 | 
| arrdb02             | code                                  |          9 | 
| arrdb02             | part_obj                              |          1 | 
| cacti               | cdef                                  |          8 | 
| cacti               | cdef_items                            |         22 | 
| cacti               | colors                                |        215 | 
...
...

The above command is selecting 3 columns from the information_schema table:

  • table_schema (database name)
  • table_name
  • table_rows

To see all the fields that it contains you can use the describe command:

$ mysql -u root -p -e "describe information_schema.tables"
+-----------------+--------------+------+-----+---------+-------+
| Field           | Type         | Null | Key | Default | Extra |
+-----------------+--------------+------+-----+---------+-------+
| TABLE_CATALOG   | varchar(512) | YES  |     | NULL    |       | 
| TABLE_SCHEMA    | varchar(64)  | NO   |     |         |       | 
| TABLE_NAME      | varchar(64)  | NO   |     |         |       | 
| TABLE_TYPE      | varchar(64)  | NO   |     |         |       | 
| ENGINE          | varchar(64)  | YES  |     | NULL    |       | 
| VERSION         | bigint(21)   | YES  |     | NULL    |       | 
| ROW_FORMAT      | varchar(10)  | YES  |     | NULL    |       | 
| TABLE_ROWS      | bigint(21)   | YES  |     | NULL    |       | 
| AVG_ROW_LENGTH  | bigint(21)   | YES  |     | NULL    |       | 
| DATA_LENGTH     | bigint(21)   | YES  |     | NULL    |       | 
| MAX_DATA_LENGTH | bigint(21)   | YES  |     | NULL    |       | 
| INDEX_LENGTH    | bigint(21)   | YES  |     | NULL    |       | 
| DATA_FREE       | bigint(21)   | YES  |     | NULL    |       | 
| AUTO_INCREMENT  | bigint(21)   | YES  |     | NULL    |       | 
| CREATE_TIME     | datetime     | YES  |     | NULL    |       | 
| UPDATE_TIME     | datetime     | YES  |     | NULL    |       | 
| CHECK_TIME      | datetime     | YES  |     | NULL    |       | 
| TABLE_COLLATION | varchar(64)  | YES  |     | NULL    |       | 
| CHECKSUM        | bigint(21)   | YES  |     | NULL    |       | 
| CREATE_OPTIONS  | varchar(255) | YES  |     | NULL    |       | 
| TABLE_COMMENT   | varchar(80)  | NO   |     |         |       | 
+-----------------+--------------+------+-----+---------+-------+

References

3
connect name_of_database;
show tables;
select count(*) from name_of_database.name_of_table;

To get a complete list of qualified table names:

select concat(table_schema, '.', table_name) from information_schema.tables;
  • thanks! how do you add a wildcard to name_of_tables. I just want to see specific tables that match a specific pattern. select count() from name_of_database._foo; ??? – Patoshi パトシ Jun 11 '13 at 15:14
  • @duckx One way is to list the table names and use sed to filter the table names you want, then reinject the result into a mysql command. There's probably a way to do it in pure MySQL, but I don't know how. You should ask SQL questions on Stack Overflow. – Gilles Jun 11 '13 at 15:44
2

After selecting the database try:

SHOW TABLE STATUS LIKE '%'

Reference:

http://www.geeksww.com/tutorials/database_management_systems/mysql/administration/mysql_tables_statistics_size_engine_no_of_rows_etc.php

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