sql - List of non-empty tables in MySQL database

Question

Can I get MySQL to return all non-empty tables in a database? Much like "SHOW TABLES" but only those that are not empty.

Answer 1

'information_schema' should be holding the relevant details. You can try

SELECT table_type,
       table_name
FROM information_schema.tables
WHERE table_rows >= 1;

to select from a selective database. You can also filter by TABLE_SCHEMA:

SELECT table_schema,
       table_type,
       table_name 
FROM information_schema.tables
WHERE table_rows >= 1
  AND TABLE_SCHEMA=?