Drop MySQL Tables by Partial Name

This is an excerpt from Adminbuntu, a site for Ubuntu Server administrators:


IMPORTANT! First back up your database!

This procedure will allow you to drop many tables at once where each table name to be dropped starts with the same string.

There are two steps in the procedure:

  • Create a MySQL statement file containing all the DROP commands called drop_commands.sql
  • Run the drop_commands.sql file

1. Create drop_commands.sql File

This creates a MySQL statement file that will drop all tables that begin with a specified string.

  • Replace STRING1 with the string to match
  • Replace USERNAMEHERE with the MySQL user to use
  • Replace PASSWORDHERE with the correct password
  • Replace DATABASENAMEHERE with the name of the database
mysql --user=USERNAMEHERE --password=PASSWORDHERE -e "SELECT table_name FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME LIKE 'STRING1%' AND TABLE_SCHEMA='DATABASENAMEHERE' " | grep -v table_name | xargs -L 1 echo "DROP TABLE " | sed "s/\$/;/" | sed -e '1 i SET FOREIGN_KEY_CHECKS = 0;'| sed -e '$s@$@\nSSET FOREIGN_KEY_CHECKS = 1;@' >drop_commands.sql

2. Execute the drop_commands.sql Command File

Examine drop_commands.sql to make sure it is doing what you want.

less drop_commands.sql

Run the drop_commands.sql text file through the mysql interpreter to drop all the selected tables.

mysql --user=USERNAMEHERE --password=PASSWORDHERE DATABASENAMEHERE < drop_commands.sql
Be Sociable, Share!