count the number of rows of each table (where table names are returned from subquery )

database, mysql, sql

Solution

You could simply query the metadata:

SELECT  Table_Name, table_rows
FROM    INFORMATION_SCHEMA.TABLES
WHERE   TABLE_TYPE = 'BASE TABLE'
AND     TABLE_SCHEMA = 'YourDatabase';

Or alternatively you will need to use a `UNION ALL`

SELECT  'T1' AS TableName, COUNT(*) AS Rows
FROM    T1
UNION ALL
SELECT  'T2' AS TableName, COUNT(*) AS Rows
FROM    T2
UNION ALL
SELECT  'T3' AS TableName, COUNT(*) AS Rows
FROM    T3;

If this needs to be dynamically done then you could use dynamic SQL:

SET @SQL = (SELECT GROUP_CONCAT('SELECT ''', 
                                TableName, 
                                ''' AS TableName, COUNT(*) AS Rows FROM ', 
                                TableName SEPARATOR ' UNION ALL ')
            FROM MainTable
            --WHERE Some condition to limit tables
            );

PREPARE stmt FROM @SQL;
EXECUTE stmt;

Example on SQL Fiddle

This essentially produces the same SQL as the `UNION ALL` solution, but creates the SQL based on the contents of your main table.

Problem

I am doing a query from a table which gives list of tables (based on different conditions list will be different). I want to show list of tablenames and their row count. How can i achieve this? I have tried ``` select count(*) from (select tablename from main_table) as t; ``` But it just return the count of entries in main_table but not the count of entries in each table. I can use system tables to get rowcount but I don't want all tables but specific tables and may need row count of specific queries. Algo is something like this ``` for tablenames in main_table where id>3: select count(*) from tablename where constraints ```

Original source