Query to count the number of tables I have in MySQL

Question

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

User · Answer

mysql  show tables    it will show the names of the tables  then the count on tables   source

User · Answer

SELECT COUNT    FROM information schema tables WHERE table schema    database name

User · Answer

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

User · Answer

SELECT COUNT    FROM information schema tables WHERE table schema    dbName     Source  This is mine   USE databasename   SHOW TABLES   SELECT FOUND ROWS

User · Answer

Hope this helps  and returns only number of tables in a database  Use database   SELECT COUNT    FROM sys tables

User · Answer

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

User · Answer

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

User · Answer

SELECT COUNT    FROM information schema tables

User · Answer

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

User · Answer

SELECT COUNT    FROM information schema tables WHERE table schema    dbo  and TABLE TYPE  BASE TABLE

User · Answer

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

User · Answer

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 gt  SELECT IFNULL table schema  Total    Database  TableCount     - gt  FROM  SELECT COUNT 1  TableCount table schema     - gt        FROM information schema tables     - gt        WHERE table schema NOT IN   information schema   mysql       - gt        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

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

Examples related to mysql