Wednesday, January 7, 2009

Deleting All Tables in a MySQL database

I recently came into a scenario where I wanted to delete all the tables in my MySQL 5.x database, but didn't want to have to drop and recreate the database (because I didn't have access to do this and was too impatient to wait for the help...) and I didn't want to have to manually enter in 50+ tables each time I needed to clean up. I finally figured out a decent solution... I grep the output from the mysqldump command, looking for only the lines that begin with the word DROP (i.e. Drop table such-and-such) and pipe those lines back into mysql. The result is something like the following, where you fill in the username, password and database with your own information... mysqldump -u [USERNAME] --password=[PASSWORD] [DATABASE] grep ^DROP mysql -u [USERNAME] --password=[PASSWORD] [DATABASE]

No comments: