Er.Rahul Nirdhar Er.Rahul Nirdhar - 6 months ago 21
SQL Question

MySQL Query to count all records from multiple table

I want count the all records from all tables I tried this but this is not working.

DELIMITER $
CREATE FUNCTION countr(p_tablename varchar(10)) RETURN int(10) BEGIN DECLARE ra int; DECLARE table_count INT;
SET ra=
(SELECT COUNT(*) INTO table_count
FROM information_schema.tables
WHERE TABLE_SCHEMA = 'dest_northwind'
AND TABLE_NAME = p_tablename); RETURN ra; END$ DELIMITER ;

Answer

This will do that...

SELECT SUM(TABLE_ROWS) 
FROM INFORMATION_SCHEMA.TABLES 
WHERE TABLE_SCHEMA = DATABASE();

Update - questioner wishes to only get the rows for a subset of tables so I've updated this to contain the sum of the individual tables or counts for those table son their own.

Sum of table rows...

SELECT SUM(TABLE_ROWS) 
FROM INFORMATION_SCHEMA.TABLES 
WHERE TABLE_SCHEMA = DATABASE()
    AND TABLE_NAME IN ('Table2','Table1');

Table rows by table...

SELECT TABLE_NAME,SUM(TABLE_ROWS) 
FROM INFORMATION_SCHEMA.TABLES 
WHERE TABLE_SCHEMA = DATABASE()
    AND TABLE_NAME IN ('Table2','Table1')
GROUP BY TABLE_NAME;