snippetsqlModerate

How to drop empty tables

Submitted by: @import:stackexchange-dba·Mar 10, 2026·

Viewed 0 times

droptablesemptyhow

Problem

Is it possible to drop all empty tables from my huge database (mysql)?

I'm looking for a sql command to automatically remove all those empty tables.

Currently, I have 305 tables in my dataset, and about 30% of them are old empty tables, that will not be used in my new application.

Just to clarify; All tables are of type=MyISAM

Solution

The idea would probably be to look for the empty tables using INFORMATION_SCHEMA.TABLES

SELECT * 
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_ROWS =  '0'
AND TABLE_SCHEMA = 'my_database_only'

Then you might be able to produce an SQL query with

SELECT CONCAT('DROP TABLE ', GROUP_CONCAT(table_name), ';') AS query
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_ROWS = '0'
AND TABLE_SCHEMA = 'my_database_only';

Quick and a bit dirty, but should actually work.

Code Snippets

SELECT * 
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_ROWS =  '0'
AND TABLE_SCHEMA = 'my_database_only'

SELECT CONCAT('DROP TABLE ', GROUP_CONCAT(table_name), ';') AS query
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_ROWS = '0'
AND TABLE_SCHEMA = 'my_database_only';

Context

StackExchange Database Administrators Q#58284, answer score: 15

Revisions (0)

No revisions yet.