Find empty tables in a MySQL 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_schema as database_name,
       table_name
   from information_schema.tables
where table_type = 'BASE TABLE'
      and table_rows = 0
      and table_schema not in('information_schema', 'sys',
                              'performance_schema', 'mysql')
      -- and table_schema = 'sakila' -- put your database name here
order by table_schema,
         table_name;

Columns

  • database_name - table database (schema) name
  • table_name - table name

Rows

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

Sample results

0
There are no comments. Click here to write the first comment.