Quick way to remove all tables in MySQL DB through *nix CLI

Tablenator!

Here’s a quick time saver (if you dev like me, it’ll come in handy!) for when you just want to drop all of the tables in a database. Of course, test it first so you know what you’ll be dropping!

Test First:

for I in mysql database_name -u user_name -p -e 'show tables' | awk '{ print $1}' | grep -v '^Tables' ; do echo "Table: $I" ; done

Code:

for I in `mysql database_name -u user_name -p -e 'show tables' | awk '{ print $1}' | grep -v '^Tables'` ; do mysql -u user_name -p -e "drop table $I" ; done

Rundown

The basic formula is a BASH for loop that grabs all of the tables in the desired database, filters the output using a combination of AWK and GREP so we can use just a simple list, then drops each table separately.

USE THIS CODE AT YOUR OWN RISK! BACKUP YOUR DATABASE FIRST AND HAVE A RECOVERY PLAN IN PLACE! (trust a man known to fat finger things)

Leave a Reply