3

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

2

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; DEALLCOATE 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 !!!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.