27 Mar 2017

Batch drop tables that share the same prefix

Occassionaly when I'm working on a site I might append an entirely new set of database tables on an existing website. After a while the database might become so large that it's neccessary to drop older tables.

Tables that share the same prefix can be dropped with the following 2 part method:

In this example, the database table prefix is _elgg

First run the following SQL command:

SELECT CONCAT('DROP TABLE ', TABLE_SCHEMA, '.', TABLE_NAME, ';') 
FROM INFORMATION_SCHEMA.TABLES 
WHERE TABLE_NAME LIKE '\_elgg%' 
AND TABLE_SCHEMA = 'your_database_name';

This will return a bunch of commands to drop each individual table. Running each of these SQL queries will infact solve this issue, but we can go one step further to automate things a little more by running the following query:

SELECT CONCAT('DROP TABLE ', TABLE_SCHEMA, '.', TABLE_NAME, ';') 
FROM INFORMATION_SCHEMA.TABLES 
WHERE TABLE_NAME LIKE '\_elgg%' 
AND TABLE_SCHEMA = 'your_database_name' 
INTO OUTFILE '/tmp/whatever_filename';

SOURCE /tmp/whatever_filename;

The same can also be achieved directly within the command line by running the following:

mysql -B databasename -uroot -prootpassword --disable-column-names  -e `mysql -B databasename -uroot -prootpassword --disable-column-names  -e "SELECT CONCAT('DROP TABLE ', TABLE_SCHEMA, '.', TABLE_NAME, ';') FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME LIKE '\_elgg%' AND TABLE_SCHEMA = 'your_database_name';"`