MySQL Snippet: List Tables and Count of Rows

In order to produce a list of all tables and the count of rows per table in MySQL there are two methods available:

Cached Estimate

As reported by other users and MySQL documentation, in this method, a cached estimate of number of rows will be returned which might not be an accurate representation of the data. However, this method is said to be less computationally heavy. The following snippet uses this method; just replace *Database_Name* with the name of your database:

SELECT SUM(TABLE_ROWS) 
FROM INFORMATION_SCHEMA.TABLES 
WHERE TABLE_SCHEMA = `*Database_Name*`;

The Manual Way

Another solution proposed by a StackOverflow user, Nathan, is listed below. This method outputs a SELECT statement with the table name and row count for each table in your database. Each SELECT statement is returned on a row and ends with a UNION keyword. You could use this output by removing the UNION keyword from the last output row, and executing the resulting MySQL script to get a list of tables and number of rows per table. This method is more computationally intensive but returns a more accurate output. Here is the snippet; again, do not forget to replace *Database_Name* with the name of your database:

SELECT CONCAT(
    'SELECT "', 
    table_name, 
    '" AS table_name, COUNT(*) AS exact_row_count FROM `', 
    table_schema,
    '`.`',
    table_name, 
    '` UNION '
) 
FROM INFORMATION_SCHEMA.TABLES 
WHERE table_schema = '*Database_Name*';

Based on: StackOverflow