How to drop empty tables

Question

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

Answer 1

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.

Answer 2

Since all tables are MyISAM, this makes my answer easier to express.

First, you need to query the INFORMATION_SCHEMA for the tables that have zero rows:

SELECT table_schema,table_name FROM information_schema.tables
WHERE table_rows = 0 AND table_schema NOT IN
('information_schema','mysql','performance_schema');

Next, formulate the query to drop the empty tables:

SELECT CONCAT('DROP TABLE ',table_schema,'.',table_name,';') DropTableCommand
FROM information_schema.tables
WHERE table_rows = 0 AND table_schema NOT IN
('information_schema','mysql','performance_schema');

Now, dump the commands into an external SQL text file.

SQL="SELECT CONCAT('DROP TABLE ',table_schema,'.',table_name,';') DropTableCommand"
SQL="${SQL} FROM information_schema.tables WHERE table_rows = 0 AND table_schema"
SQL="${SQL} NOT IN ('information_schema','mysql','performance_schema')"
mysql -uroot -p -ANe"${SQL}" > DropTables.sql

Look at the contents with one of the following

• less DropTables.sql
• cat DropTables.sql

If you are satisfied with its contents, run the script:

mysql -uroot -p < DropTables.sql

or login to mysql and run it like this:

mysql> source DropTables.sql

Give it a Try !!!

CAVEAT : This technique only works with MyISAM table because the row count of a MyISAM table is physically stored in the .MYD of the tables. The INFORMATION_SCHEMA.TABLES metadata table is always reading this and updating. DO NOT TRY THIS WITH INNODB !!!

UPDATE 2014-02-05 11:46 EST

There is a reason I excluded ('information_schema','mysql','performance_schema')

The mysql schema has empty tables in it. Some MyISAM, some InnoDB, some CSV.

For example, here are my tables in the mysql schema for MySQL 5.6.15 on my desktop

mysql> select table_name,engine,table_rows
    -> from information_schema.tables
    -> where table_schema='mysql';
+---------------------------+--------+------------+
| table_name                | engine | table_rows |
+---------------------------+--------+------------+
| columns_priv              | MyISAM |          0 |
| db                        | MyISAM |          2 |
| event                     | MyISAM |          0 |
| func                      | MyISAM |          0 |
| general_log               | CSV    |          2 |
| help_category             | MyISAM |         40 |
| help_keyword              | MyISAM |        485 |
| help_relation             | MyISAM |       1090 |
| help_topic                | MyISAM |        534 |
| innodb_index_stats        | InnoDB |          0 |
| innodb_table_stats        | InnoDB |          0 |
| ndb_binlog_index          | MyISAM |          0 |
| plugin                    | MyISAM |          0 |
| proc                      | MyISAM |          0 |
| procs_priv                | MyISAM |          0 |
| proxies_priv              | MyISAM |          1 |
| servers                   | MyISAM |          0 |
| slave_master_info         | InnoDB |          0 |
| slave_relay_log_info      | InnoDB |          0 |
| slave_worker_info         | InnoDB |          0 |
| slow_log                  | CSV    |          2 |
| tables_priv               | MyISAM |          0 |
| time_zone                 | MyISAM |          0 |
| time_zone_leap_second     | MyISAM |          0 |
| time_zone_name            | MyISAM |          0 |
| time_zone_transition      | MyISAM |          0 |
| time_zone_transition_type | MyISAM |          0 |
| user                      | MyISAM |          6 |
+---------------------------+--------+------------+
28 rows in set (0.01 sec)

mysql>

If some of those tables were to disappear (like columns_priv, proc_priv, tables_priv, etc.), the grant mechanism may not work properly or may cause mysqld not to start up. You aso do not want to whack mysql.proc since it is the physical home of Stored Procedures. Other mechanisms may not work either should you want to use them, such as CrashSafe Replication using the InnoDB tables inside the mysql schema, or if you want to insert timezone information.

UPDATE 2014-02-05 12:36 EST

I would like to commend Tom Desp for his answer for a specific reason: His syntax could do the drop without using an external script. Using his idea, let me capture the DROP TABLE command into a user-defined variable.

SELECT CONCAT('DROP TABLE ',GROUP_CONCAT(DBTB),';')
INTO @DropCommand
FROM (SELECT CONCAT(table_schema,'.',table_name) DBTB
FROM information_schema.tables
WHERE table_rows = 0 AND table_schema NOT IN
('information_schema','mysql','performance_schema')) A;
SELECT @DropCommand;

If the output of the SELECT @DropCommand; is correct, then execute the command like this:

PREPARE s FROM @DropCommand;
EXECUTE s;
DEALLOCATE PREPARE s;

This eliminates two things:

• the need for an external SQL text file
• running a separate DROP TABLE command for each table