-1

I would like to know how to Count the TOTAL number of rows in the tables of a database. For example, I have 3 tables in a database called:

Table 1: 5 rows

Table 2: 4 rows

Table 3: 3 rows

So the output should be: 12 rows.

I know how to count rows in a specific table. Is there something to loop here?

Thanks.

DotNet ZeZo
  • 19
  • 2
  • https://dev.mysql.com/doc/refman/5.1/en/tables-table.html shows a schema table containing a rough count of the rows. if you need an exact count, you could use data in the information schema to query each table and get the exact counts. – xQbert Apr 10 '14 at 20:42
  • Thanks Rob. That worked out. – DotNet ZeZo Apr 10 '14 at 20:53

3 Answers3

1
SELECT sum(cnt) AS overall_cnt
FROM (
    SELECT count(*) AS cnt FROM table1
    UNION ALL
    SELECT count(*) AS cnt FROM table2
    UNION ALL
    SELECT count(*) AS cnt FROM table3
) AS foo
Marc B
  • 356,200
  • 43
  • 426
  • 500
  • No no. I mean what if I have like thousands of tables? Is there like a loop or something? – DotNet ZeZo Apr 10 '14 at 20:50
  • not in a regular query. you could build a stored procedure that queries the information schema tables and loop on those results. – Marc B Apr 10 '14 at 20:52
0
SELECT sum(TABLE_ROWS)
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME IN ('table1', 'table2', 'table3');
Umren
  • 392
  • 4
  • 12
0
SELECT SUM(TABLE_ROWS)
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'db_name';

enter image description here

The Blue Dog
  • 2,475
  • 3
  • 19
  • 25