How to Drop the Mysql Tables but not the Database

Here’s an easy way to clear out a Mysql database of all of its tables.

Using mysqldump

user=USER ; pass=PASS ; host=localhost ; database=DATABASE ; mysqldump -u${user} -p${pass} -h ${host} --add-drop-table --no-data ${database} | grep ^DROP | mysql -u${user} -p${pass} -h ${host} ${database}

It works by getting the dump of the database, looking for the DROP statements, and then dropping those tables.


Using an SQL command/pattern

You can also use LIKE to drop all the tables that match a pattern:

DROP TABLE LIKE '%pattern%'