Category Archives: mysql

Drop MySQL Tables by Partial Name

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

http://www.adminbuntu.com/drop_mysql_tables_by_partial_name

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

Drop All Tables in a MySQL Database

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

http://www.adminbuntu.com/drop_all_tables_in_a_database

IMPORTANT: This is a dangerous command. Back up the database first!

Drop all tables in a database, without dropping the database itself.

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

  • 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=USERNAMEHERE -BNe "SHOW TABLES" DATABASENAMEHERE | tr '\n' ',' | sed -e 's/,$//' | awk '{print "SET FOREIGN_KEY_CHECKS = 0;DROP TABLE IF EXISTS " $1 ";SET FOREIGN_KEY_CHECKS = 1;"}' | mysql --user=USERNAMEHERE --password=USERNAMEHERE DATABASENAMEHERE