Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

MYSQL - count number of rows in each table

Tags:

sql

mysql

I would like to know how many rows are in each table in my database. I've come so far as to having

select count(*) _tablename_;  

However i would need to do that on each and every table - and there are a lot. What would me the best way to get a print-out with the table name and it's row count?

like image 712
Redconnection Avatar asked Jan 05 '11 10:01

Redconnection


People also ask

How do I find the number of rows in each table in MySQL?

To get the count of rows, you need to use information_schema. tables. The syntax is as follows. SELECT table_name, table_rows FROM INFORMATION_SCHEMA.

How do I find the number of rows in each table of the schema?

This can be achieved by using the following query. SELECT table_schema, SUM(row_count) AS total_rows FROM ( SELECT table_schema, count_rows_of_table(table_schema, table_name) AS row_count FROM information_schema.

How do I count rows in a table in SQL?

Use the COUNT aggregate function to count the number of rows in a table. This function takes the name of the column as its argument (e.g., id ) and returns the number of rows for this particular column in the table (e.g., 5).


1 Answers

SELECT table_name, table_rows     FROM INFORMATION_SCHEMA.TABLES     WHERE TABLE_SCHEMA = '<your db>'; 

I also hope you realise there's an error in your query: it's missing a FROM.

like image 128
moinudin Avatar answered Oct 04 '22 15:10

moinudin