A query to count the number of tables that I have in MySQL

Question

I am increasing the number of tables that I have, and I am sometimes curious just to run a quick command line query to count the number of tables in my database. Is it possible? If so, what is the request?

Answer 1

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

Source

It is mine:

 USE databasename; SHOW TABLES; SELECT FOUND_ROWS(); 

Answer 2

If you want to count all databases and resumes, try the following:

 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 an example 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 !!!

Answer 3

 SELECT COUNT(*) FROM information_schema.tables WHERE table_schema = 'dbo' and TABLE_TYPE='BASE TABLE' 

Answer 4

To count the number of tables, do the following:

 USE your_db_name; -- set database SHOW TABLES; -- tables lists SELECT FOUND_ROWS(); -- number of tables 

Sometimes light things will do the job.

Answer 5

This will give you the names and number of tables of all the databases in your mysql

 SELECT TABLE_SCHEMA,COUNT(*) FROM information_schema.tables group by TABLE_SCHEMA; 

Answer 6

 SELECT COUNT(*) FROM information_schema.tables 

Answer 7

There are several ways to count database tables. My favorite is:

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

Answer 8

 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'; 

Answer 9

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

Answer 10

from the command line:

 mysql -uroot -proot -e "select count(*) from information_schema.tables where table_schema = 'database_name';" 

in the above example, root is the username and password hosted on the local hosting.

Answer 11

You can use the query below to find the total number of tables in your database.

 select count(*) from information_schema.tables 

Answer 12

We hope this helps, and returns only the number of tables in the database

 Use database; SELECT COUNT(*) FROM sys.tables;