mysqlshow with the number of tables

Question

Following command will let me know the names of databases.

$ mysqlshow

But how do I know the number of tables in each database and also the number of empty tables? for e.g.

db count empty

test 10 5

mydb 122 0

client 34 34

Answer 1

You can use

SHOW TABLES;

To get the tables in a database and then count the rows returned.

You can also do this (though it can be very slow):

 SELECT COUNT(*) FROM information_schema.tables WHERE table_schema = 'my_database';

Answer 2

You can select count(*) from information_schema.tables if you have priviliges on it.

select count(*) from information_schema.tables
where table_schema = <My Schema>
and table_type = 'BASE TABLE';

and filter on table_rows column for empty yables:

 select count(*) from information_schema.tables
 where table_schema = <My Schema>
 and table_type = 'BASE TABLE'
 and table_rows = 0;

Answer 3

SHOW DATABASES;
SHOW TABLES FROM --your_db_here--;
SELECT COUNT(*) = 0 FROM --your_table_here--;

Answer 4

there is a special database, information_schema which contains meta data about all databases on the mysql server.

SELECT `TABLE_NAME`, `TABLE_ROWS` 
FROM `information_schema`.`TABLES` 
WHERE `TABLE_SCHEMA` = 'NameOfDatabaseYouAreInterestedIn'
AND `TABLE_TYPE` = 'BASE TABLE'

TABLE_ROWS is not always completely accurate, you may want to loop through the tables and get a count

Answer 5

I dont know if this helps but there is a option to mysqlshow to show number of rows per table (--count):

$mysqlshow --count  *p*
Wildcard: %p%
+-------------+--------+--------------+
|  Databases  | Tables |  Total Rows  |
+-------------+--------+--------------+
| implantacao |     25 |          134 |
| pmsp        |     80 |      8561947 |
| tmp         |      7 |            5 |
+-------------+--------+--------------+
3 rows in set.

And if you pass the database name:

$mysqlshow --count tmp
Database: tmp
+------------+----------+------------+
|   Tables   | Columns  | Total Rows |
+------------+----------+------------+
| builds     |        2 |          0 |
| gtable     |        2 |          5 |
| patterns   |        9 |          0 |
| products   |        2 |          0 |
| sig_types  |        2 |          0 |
| signatures |        2 |          0 |
| versions   |        2 |          0 |
+------------+----------+------------+
7 rows in set.

You can add the tablename and get info about it:

$mysqlshow tmp gtable
Database: tmp  Table: gtable
+--------+---------+-------------------+------+-----+---------+-------+---------------------------------+---------+
| Field  | Type    | Collation         | Null | Key | Default | Extra | Privileges                      | Comment |
+--------+---------+-------------------+------+-----+---------+-------+---------------------------------+---------+
| symbol | text    | latin1_swedish_ci | YES  |     |         |       | select,insert,update,references |         |
| size   | int(11) |                   | YES  |     |         |       | select,insert,update,references |         |
+--------+---------+-------------------+------+-----+---------+-------+---------------------------------+---------+

Look at mysqlshow --help for more options/information.