CODEWITHSUNDEEP
sqlmysqlcount
Lee
Lee

Reputation: 20934

MySQL - How to count all rows per table in one query

Is there a way to query the DB to find out how many rows there are in all the tables?

i.e.

table1 1234
table2 222
table3 7888

Hope you can advise

Upvotes: 74

Views: 78440

Answers (9)

Uttam Burman
Uttam Burman

Reputation: 11

Run this Query a to get results, Information Schema won't give correct results.

Select group_concat(Query SEPARATOR ' union all ') as Full_Query from (SELECT CONCAT('SELECT ''',table_name,''', COUNT(*) FROM ', table_name) as Query
FROM information_schema.tables) AS T1 into @sql from (select 
table_schema db,
table_name tablename from information_schema.tables where table_schema not in 
('performance_schema', 'mysql', 'information_schema')) t;

Then Run-

prepare s from @sql; execute s; deallocate prepare s;

Upvotes: 0

Jimmix
Jimmix

Reputation: 6506

It is convenient to use a stored procedure to get tables' rows. For example:

CALL database_tables_row_count('my_shop_db');

will display:

+-------------------+-----------+
| table             | row_count |
+-------------------+-----------+
| user              |         5 |
| payment           |        12 |
+-------------------+-----------+

in case there are no tables inside 'my_shop_db' you'll get:

Empty set (0.00 sec)

If you misspell the database name you'll get:

ERROR 1049 (42000): Unknown database 'my_so_db'

The same way as if you issued the statement use non_existing_db;

The stored procedure must be stored somewhere (in a database). If you store it into the current database, you will be able to use it this way

CALL database_tables_row_count('my_shop_db');

to get the results regarding any database as long you use your current database where you stored that procedure

Since such query like a count of tables' rows is quite common so you may want to store that procedure in a common database (a kind of a toolbox) for eg called admin. To create the stored procedure inside a new db:

CREATE DATABASE IF NOT EXISTS `admin`;

then switch into it:

USE `admin`;

and create the stored procedure:

DROP PROCEDURE IF EXISTS `database_tables_row_count`;

DELIMITER $$
CREATE PROCEDURE `database_tables_row_count`(IN tableSchema VARCHAR(255))
BEGIN
    DECLARE msg VARCHAR(128);

    IF (SELECT COUNT(TABLE_NAME) FROM information_schema.tables WHERE table_schema = `tableSchema`) = 0 THEN
        SET msg = CONCAT('Unknown database \'', `tableSchema`, '\'');
        SIGNAL SQLSTATE '42000' SET MESSAGE_TEXT = msg, MYSQL_ERRNO = 1049;
    END IF;

    SET SESSION group_concat_max_len = 10000000;
    SET @rowCounts = (
        SELECT group_concat(CONCAT('SELECT ''',TABLE_NAME,''' AS `table`, COUNT(*) AS `row_count` FROM ', `tableSchema`, '.', TABLE_NAME) SEPARATOR ' union all ')
        FROM information_schema.tables WHERE table_schema = `tableSchema`
        AND TABLE_TYPE = 'BASE TABLE'
    );

    IF @rowCounts IS NOT NULL THEN
        PREPARE statement FROM @rowCounts;
        EXECUTE statement;
        DEALLOCATE PREPARE statement;
    ELSE
        # if no base tables found then return an empty set
        select 1 where 0 = 1;
    END IF;

END$$
DELIMITER ;

Then to use it despite the current database:

CALL admin.database_tables_row_count('my_shop_db');

to get the results.

There is no need to create a separate database just to hold that procedure but I found useful to have a one dedicated database that acts as a kind of toolbox so I don't need to recreate procedures/views/functions for every development and every time after I used drop database...

You may want to change this line:

AND TABLE_TYPE = 'BASE TABLE'

to:

AND TABLE_TYPE IN ('BASE TABLE', 'VIEW')

if you want to get rows count also for the views.

Upvotes: 2

ande
ande

Reputation: 125

The above will give you an approximation, but if you want an exact count, it can be done in two steps. First, execute a query like the following:

select concat("select '",table_name,"', count(*) from ",table_name,";") 
from `information_schema`.`tables` 
WHERE `table_schema` = '[your schema here]';

That will produce a list of SQL statements, one for each table in your database, you can then run to get an exact count.

Upvotes: 10

a1ex07
a1ex07

Reputation: 37354

select sum(cnt) from
(
select count(*) as cnt from table1
union ALL
select count(*) as cnt from table2
union ALL
select count(*) as cnt from table3 
)t1

Upvotes: 4

Nir
Nir

Reputation: 25359

SELECT 
    table_name, 
    table_rows 
FROM 
    INFORMATION_SCHEMA.TABLES

Upvotes: 7

great_llama
great_llama

Reputation: 11729

SELECT 
    TABLE_NAME, 
    TABLE_ROWS 
FROM 
    `information_schema`.`tables` 
WHERE 
    `table_schema` = 'YOUR_DB_NAME';

Upvotes: 171

OneSimpleGeek
OneSimpleGeek

Reputation: 148

Probably want this if you just want tables and no views:

SELECT TABLE_NAME, TABLE_ROWS
FROM   `information_schema`.`tables` 
WHERE  `table_schema` = 'schema'
       AND TABLE_TYPE = 'BASE TABLE';

Upvotes: 0

tjmcewan
tjmcewan

Reputation: 2528

Synthesising the info above and this post into one set of queries, we get a self-writing query that will give accurate row counts:

SET @tableSchema = 'my_schema';
SET SESSION group_concat_max_len = 10000000;
SET @rowCounts = (
  SELECT group_concat(CONCAT('SELECT ''',TABLE_NAME,''', COUNT(*) FROM ', TABLE_NAME) SEPARATOR ' union all ')
  FROM information_schema.tables WHERE table_schema = @tableSchema
);
PREPARE statement FROM @rowCounts;
EXECUTE statement;

-- don't run dealloc until you've exported your results ;)
DEALLOCATE PREPARE statement;

Upvotes: 14

petrichi
petrichi

Reputation: 101

This will give you the exact Table name and count on a single list

SELECT CONCAT('SELECT ''',table_name,''', COUNT(*) FROM ', table_name, ' union all') 
      FROM information_schema.tables WHERE table_schema = 'clw';

Upvotes: 6

Related Questions