How to get row count from all tables of a schema ( without using information schema ) The query should collect data from table name , schema name from information.schema and row count should be taken from actual table.
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&#39;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