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';"`