Take the 2-minute tour ×
Unix & Linux Stack Exchange is a question and answer site for users of Linux, FreeBSD and other Un*x-like operating systems.. It's 100% free, no registration required.

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

share|improve this question

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.

    
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. –  duckx Sep 24 at 14:56

3 Answers 3

up vote 0 down vote accepted

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

share|improve this answer
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;
share|improve this answer
    
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; ??? –  duckx 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

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