This query returns a list of tables in a database (schema) without rows.
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.
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;
- database_name - table database (schema) name
- table_name - table name
- One row: represents one table
- Scope of rows: only empty tables (without rows)
- Ordered by: database (schema) and table name