Dropping multiple tables in mysql (drop tables with wildcard)
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.
