Adminbuntu

Everything for the Ubuntu Server Administrator

User Tools

Site Tools


Sidebar

Server Administration


Server Applications


At the Command Line


Elsewhere


Copyright 2013 Applied Conscious Technologies, LLC

Terms of Agreement

Contact


submit to reddit

drop_mysql_tables_by_partial_name

MySQL

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:

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

See also: Dump MySQL Tables by Partial Name


1. Create drop_commands.sql File

This creates a MySQL statement file that will drop all tables that begin with a certain 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

Because the mysql command output is being piped through a filter, the output is stripped of the formatting you see when just entering that single command at a terminal.

How it Works

sub-command purpose
mysql command Outputs a table containing matching table names.
grep -v table_name Strips off the header line.
xargs -L 1 echo “DROP TABLE ” Prepends each line with the drop command.
sed “s/\$/;/” Appends each line with a semicolon.
sed -e '1 i SET FOREIGN_KEY_CHECKS = 0;' Prepends the file with a line to turn off foreign key checks.
sed -e '1 i SET FOREIGN_KEY_CHECKS = 1;' Appends the file with a line to turn on foreign key checks.
>drop_commands.sql Saves the output to a text file called 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_mysql_tables_by_partial_name.txt · Last modified: 2015/05/31 21:20 (external edit)