CODEWITHSUNDEEP
mysqlsqldatabase
Gaurav
Gaurav

Reputation: 2103

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

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

Upvotes: 4

Views: 7871

Answers (3)

Angel T
Angel T

Reputation: 182

Here is the example with dynamic SQL, if you want to create table with three columns:
1st is Table Name and contains table names of main_table, 2nd is Column X, contain single quote (‘) char(s) in column User Name of main_table and 3rd is Rows which contains count of rows of each table in Table Name:

    SET @SQL = (select GROUP_CONCAT('SELECT ''', 
                                    `Table Name `,
                                    ''' AS `Table Name`,
                                    ', quote(`User Name`), ',
                                    COUNT(*) AS Rows FROM `',
                                    `Table Name` SEPARATOR '` UNION ALL ')
                                    FROM `.tables`
               );
    PREPARE stmt FROM @SQL;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;

Upvotes: 0

GarethD
GarethD

Reputation: 69759

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.

Upvotes: 3

WernerW
WernerW

Reputation: 812

Try something like this:

SELECT table_name, table_type, SUM(TABLE_ROWS) 
FROM information_schema.tables
WHERE table_schema = 'db5' -- or your own schema
    AND  table_type ='BASE TABLE'
    AND table_name LIKE 'mytables%'
GROUP BY table_name, table_type

Or this

SELECT table_name, TABLE_ROWS FROM INFORMATION_SCHEMA.TABLES 
WHERE TABLE_SCHEMA = '{your_db}';

Upvotes: 5

Related Questions