How to Get Record Counts for All Tables

How to Get Record Counts for All Tables

The following methods can be used to get the record count for all tables in MySQL....

Demo MySQL Database

First, we’ve to create a Database with the help of the query below we’ll name it ‘demodb’....

Using INFORMATION_SCHEMA.TABLES with SUM function to Get Record Count

This approach involves retrieving the sum of the estimated row counts for all tables within the specified database (yourDatabaseName). It operates on the INFORMATION_SCHEMA.TABLES system view, which contains metadata about database tables. The TABLE_ROWS column in this view provides an approximate row count for each table and then aggregate function SUM gives total count of rows/records....

Using COUNT(*) to Get Record Count

In this method, use various subqueries, i.e., one subquery for each individual table record count....

Using UNION to Get Record Count

The UNION operation combines rows from both queries. The operator combines the result returned by the individual SELECT query....

Conclusion

This guide explained three distinct approaches to count records in a MySQL database. Using the TABLE_ROWS attribute with the aggregate function SUM and employing a direct count of rows for each table. All approaches offer unique insights into the record counts in database....