CODEWITHSUNDEEP
mysqlcount
user1856596
user1856596

Reputation: 7233

MySQL: Query to get the count of each table in a DB?

How could I get the count of rows from each table in a certain DB including the tables name? I know how to get the table count and the sum of rows for all tables, but not how to get the count for each table separately.

Upvotes: 0

Views: 81

Answers (3)

miguelbgouveia
miguelbgouveia

Reputation: 2973

I think this query resolve your problem:

SELECT ‘table1’, (SELECT COUNT() FROM Table1) AS CountTable1, ‘table2’, (SELECT COUNT() FROM Table2) AS CountTable2

Upvotes: 0

hjpotter92
hjpotter92

Reputation: 80639

If all the tables are inside a single database, let's assume it to be "test123", then you can use the following command to fetch number of rows:

SHOW TABLE STATUS 
FROM `test123`;

It'll return result containing the following values:

`Name`, `Engine`, `Version`, 
`Row_format`, `Rows`, `Avg_row_length`, 
`Data_length`, `Max_data_length`, `Index_length`, 
`Data_free`, `Auto_increment`, `Create_time`, 
`Update_time`, `Check_time`, `Collation`, 
`Checksum`, `Create_options`, `Comment`

Upvotes: 0

ajtrichards
ajtrichards

Reputation: 30565

You could try something like this:

SELECT TABLE_ROWS, TABLE_NAME
     FROM INFORMATION_SCHEMA.TABLES 
     WHERE TABLE_SCHEMA = 'DB_NAME'

Upvotes: 1

Related Questions