[mysql] Query to count the number of tables I have in MySQL

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

The answer is


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.


mysql> show tables;

it will show the names of the tables, then the count on tables.

source


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.