Find empty tables in MariaDB database

This query returns a list of tables in a database (schema) without rows.

Notes

Some storage engines, such as MyISAM, store the exact count. For other storage engines, such as InnoDB, this value is an approximation, and may vary from the actual value by as much as 40% to 50%. In such cases, use SELECT COUNT(*) to obtain an accurate count.

Query

select table_name as 'table'
   from information_schema.tables
where table_schema = 'your database name' -- put your database name here
    and table_type = 'BASE TABLE'
    and table_rows = 0
order by concat(table_schema, '.', table_name);

Columns

  • table - table name with database (schema) name

Rows

  • One row: represents one table
  • Scope of rows: only empty tables (without rows)
  • Ordered by: database (schema) and table name

Sample results

Empty tables in the Airlines database (schema).