I am growing the number of tables I have and I am sometimes curious just to do a quick command line query to count the number of tables in my database. Is that possible? If so, what is the query?
This question is related to
mysql
In case you would like a count all the databases plus a summary, please try this:
SELECT IFNULL(table_schema,'Total') "Database",TableCount
FROM (SELECT COUNT(1) TableCount,table_schema
FROM information_schema.tables
WHERE table_schema NOT IN ('information_schema','mysql')
GROUP BY table_schema WITH ROLLUP) A;
Here is a sample run:
mysql> SELECT IFNULL(table_schema,'Total') "Database",TableCount
-> FROM (SELECT COUNT(1) TableCount,table_schema
-> FROM information_schema.tables
-> WHERE table_schema NOT IN ('information_schema','mysql')
-> GROUP BY table_schema WITH ROLLUP) A;
+--------------------+------------+
| Database | TableCount |
+--------------------+------------+
| performance_schema | 17 |
| Total | 17 |
+--------------------+------------+
2 rows in set (0.29 sec)
Give it a Try !!!
This will give you names and table count of all the databases in you mysql
SELECT TABLE_SCHEMA,COUNT(*) FROM information_schema.tables group by TABLE_SCHEMA;
Hope this helps, and returns only number of tables in a database
Use database;
SELECT COUNT(*) FROM sys.tables;
There may be multiple ways to count the tables of a database. My favorite is this on:
SELECT
COUNT(*)
FROM
`information_schema`.`tables`
WHERE
`table_schema` = 'my_database_name'
;
To count number of tables just do this:
USE your_db_name; -- set database
SHOW TABLES; -- tables lists
SELECT FOUND_ROWS(); -- number of tables
Sometimes easy things will do the work.
SELECT COUNT(*) FROM information_schema.tables WHERE table_schema = 'database_name';
select name, count(*) from DBS, TBLS
where DBS.DB_ID = TBLS.DB_ID
group by NAME into outfile '/tmp/QueryOut1.csv'
fields terminated by ',' lines terminated by '\n';
SELECT COUNT(*) FROM information_schema.tables WHERE table_schema = 'dbo' and TABLE_TYPE='BASE TABLE'
SELECT COUNT(*) FROM information_schema.tables
from command line :
mysql -uroot -proot -e "select count(*) from
information_schema.tables where table_schema = 'database_name';"
in above example root is username and password , hosted on localhost.
Source: Stackoverflow.com