How to get row count from all tables of a schema ( without using information schema )

Question

The query should collect data from table name , schema name from information.schema and row count should be taken from actual table.

Answer 1

This is actually a very good question since MyISAM stores the count in its header, whereas InnoDB requires a full count (See my answer to the post Why doesn't InnoDB store the row count?)

SOLUTION

Use INFORMATION_SCHEMA.TABLES to create the SQL for counting each table

ALL DATABASES

SET group_concat_max_len = 1048576;
SELECT GROUP_CONCAT(CONCAT('SELECT ',QUOTE(db),' table_schema,',QUOTE(tb),
' table_name,COUNT(1) table_rows FROM `',db,'`.`',tb,'`') SEPARATOR ' UNION ')
INTO @CountSQL
FROM (SELECT table_schema db,table_name tb FROM information_schema.tables WHERE
table_schema NOT IN ('information_schema','performance_schema','mysql')) A;
SELECT @CountSQL\G
PREPARE s FROM @CountSQL; EXECUTE s; DEALLOCATE PREPARE s;

CURRENT DATABASE

SET group_concat_max_len = 1048576;
SELECT GROUP_CONCAT(CONCAT('SELECT ',QUOTE(db),' table_schema,',QUOTE(tb),
' table_name,COUNT(1) table_rows FROM `',db,'`.`',tb,'`') SEPARATOR ' UNION ')
INTO @CountSQL
FROM (SELECT table_schema db,table_name tb FROM information_schema.tables WHERE
table_schema = DATABASE()) A;
SELECT @CountSQL\G
PREPARE s FROM @CountSQL; EXECUTE s; DEALLOCATE PREPARE s;

SPECIFIC DATABASE (like mydata)

SET group_concat_max_len = 1048576;
SELECT GROUP_CONCAT(CONCAT('SELECT ',QUOTE(db),' table_schema,',QUOTE(tb),
' table_name,COUNT(1) table_rows FROM `',db,'`.`',tb,'`') SEPARATOR ' UNION ')
INTO @CountSQL
FROM (SELECT table_schema db,table_name tb FROM information_schema.tables WHERE
table_schema = 'mydata') A;
SELECT @CountSQL\G
PREPARE s FROM @CountSQL; EXECUTE s; DEALLOCATE PREPARE s;

To see the SQL that is generated run this

SELECT @CountSQL\G

GIVE IT A TRY !!!

See docs at https://dev.mysql.com/doc/refman/8.0/en/tables-table.html