CODEWITHSUNDEEP
mysql
Genadinik
Genadinik

Reputation: 18639

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?

Upvotes: 154

Views: 229096

Answers (7)

Mukund Thakkar
Mukund Thakkar

Reputation: 1295

If you just want to get the count of tables without views you can do it like this:

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

Upvotes: 16

Mohammed Nosirat
Mohammed Nosirat

Reputation: 372

mysql> show tables;

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

source

Upvotes: 1

Joseadrian
Joseadrian

Reputation: 4374

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

Source

This is mine:

USE databasename; 
SHOW TABLES; 
SELECT FOUND_ROWS();

Upvotes: 338

Rajiv Singh
Rajiv Singh

Reputation: 11

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.

Upvotes: 1

Rohit Dubey
Rohit Dubey

Reputation: 1294

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;

Upvotes: 7

automatix
automatix

Reputation: 14532

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

Upvotes: 1

RolandoMySQLDBA
RolandoMySQLDBA

Reputation: 44343

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

Upvotes: 25

Related Questions