Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question
1  
Are all tables MyISAM, InnoDB, or a mixture of both ??? –  RolandoMySQLDBA Feb 5 at 15:03
    
They are all MyISAM –  qualbeen Feb 5 at 15:46
add comment

2 Answers

up vote 6 down vote accepted

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.

share|improve this answer
3  
... and then you have a broken database server because you have "probably" deleted some of the mysql system tables... besides the fact that your CONCAT produces a table list without schema reference. –  Cristian Porta Feb 5 at 16:42
1  
You also have the "right" to smartly add database restrictions to your tables, I thought it was obvious. –  Tom Desp Feb 5 at 16:53
2  
the answer should not take anything for granted because the answer are potentially dangerous for the user... that's all –  Cristian Porta Feb 5 at 17:01
2  
+1 for setting up a single DROP TABLE command. –  RolandoMySQLDBA Feb 5 at 17:37
1  
@TomDesp we're here to help us ;) ... I agree with you about the possibility to drop system tables! –  Cristian Porta Feb 5 at 19:05
show 3 more comments

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
share|improve this answer
    
Thanks for a long and good explanation! +1 for returning tables across databases. This way it is easy to clean up several dataset. On the other hand; use with care. I run your query against "..AND table_schema = 'my_database_name'" to only work with one database (as Tom Desp suggested in an other reply) –  qualbeen Feb 6 at 9:45
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.