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
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
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';
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;
SHOW DATABASES;
SHOW TABLES FROM --your_db_here--;
SELECT COUNT(*) = 0 FROM --your_table_here--;
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
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.