The query should collect data from table name , schema name from information.schema and row count should be taken from actual table.
MySQL – How to Get Row Count from All Tables of a Schema Without Using Information Schema
countinformation-schemaMySQLmysql-5.6
Related Question
- Mysql – Creating a query to pull very specific information using four tables
- Postgresql – Schema name, table name and row count
- Mysql – Get distinct count from row and display in sepearte column
- Mysql – count null values of table from information_schema tables
- Oracle: Get the row count in a cursor when using a OPEN-FOR-USING statement
- Mysql – Get count of value row
- PostgreSQL – Get Combined Row Count by Month from Multiple Tables
Best Answer
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
CURRENT DATABASE
SPECIFIC DATABASE (like
mydata
)To see the SQL that is generated run this
GIVE IT A TRY !!!
See docs at https://dev.mysql.com/doc/refman/8.0/en/tables-table.html