I wanted to drop all mysql tables from a db that had a certain prefix, such as xx_.

Well this turned out to be an adventure…

The First Attempt

The first solution I found was to use sed/grep sort of logic:

#mysqlshow -u username -p dbname xx\\_% |sed 's/[|+-]//g'|sed 's/[ ]*$/,/'>droptables.sql

(xx\\_% is how you tell it to show tables starting with xx_)

This produced a semi-useful list. But I had to manually edit out the extra commas and line feeds to get a pure list, then add “DROP TABLE ” to the beginning of the list. Then feed this into mysql as follows:

#mysql -u username -p dbname < droptables.sql

I wasn’t real happy with that answer, naturally, so I researched more…

A Better Solution

I came across an alternative in this command:

mysqldump -u username -p --add-drop-table --no-data dbname | grep "^DROP.*\`xx_" | mysql -u username -p dbname

(replace xx_ with the prefix you want to remove, to do all tables, try just “grep ^DROP”

You can test it first by cutting off the last “| mysql…” bit and see the output, which is useful for debugging, before you go blowing away your tables.