Consulta para contar el número de tablas que tengo en MySQL

Estoy aumentando el número de tablas que tengo y a veces tengo curiosidad por hacer una consulta rápida de línea de comandos para contar el número de tablas en mi base de datos. Es eso posible? Si es así, ¿cuál es la consulta?

 95
mysql
Author: Undo, 2011-03-05
Source

12 answers

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

Fuente

Esto es mío: 

USE databasename; 
SHOW TABLES; 
SELECT FOUND_ROWS();
 238
Author: Joseadrian,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2017-12-04 07:28:15

En caso de que desee contar todas las bases de datos más un resumen, intente esto: 

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;

Aquí hay una ejecución de ejemplo: 

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)

Dale una oportunidad !!!

 24
Author: RolandoMySQLDBA,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2013-04-16 15:14:09 
SELECT COUNT(*) FROM information_schema.tables WHERE table_schema = 'dbo' and TABLE_TYPE='BASE TABLE'
 8
Author: Mukund,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2013-03-06 05:33:06

Para contar el número de tablas simplemente haga esto: 

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

A veces las cosas fáciles harán el trabajo.

 2
Author: jmm,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2016-09-01 09:34:09

Esto le dará nombres y recuento de tablas de todas las bases de datos en su mysql 

SELECT TABLE_SCHEMA,COUNT(*) FROM information_schema.tables group by TABLE_SCHEMA;
 2
Author: Rohit Dubey,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2017-01-20 05:54:51

Puede haber varias formas de contar las tablas de una base de datos. Mi favorito es este en:

SELECT
    COUNT(*)
FROM
    `information_schema`.`tables`
WHERE
    `table_schema` = 'my_database_name'
;
 1
Author: automatix,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2016-09-01 07:49:59 
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';
 1
Author: Swamy,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2016-10-21 07:10:35 
SELECT COUNT(*) FROM information_schema.tables WHERE table_schema = 'database_name';
 1
Author: Arun Kasyakar,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2017-04-20 12:14:18

Desde la línea de comandos : 

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

En el ejemplo anterior root es nombre de usuario y contraseña , alojado en localhost.

 1
Author: Rajiv Singh,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2017-04-23 19:38:26

Puede usar la siguiente consulta para encontrar el número total de tablas en su base de datos.

select count(*) from information_schema.tables
 1
Author: rashedcs,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2017-08-09 03:30:34 
SELECT COUNT(*) FROM information_schema.tables
 0
Author: Shan,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2016-07-27 09:46:09

Espero que esto ayude, y devuelve solo el número de tablas en una base de datos

Use database;

SELECT COUNT(*) FROM sys.tables;
 -1
Author: mjohnbatcha,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2012-01-24 06:18:05