2

In my database there is a lot of tables starting with _elgg , now I want to drop all tables with this prefix. Can anyone give me a solution ?

Thanks in advance!

5

First do a

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

and check if this returns the correct statements for every table. If you want to execute those statements, do

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;

Another way: On the command line, to do it in one rush, you can do

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';"`
  • first step returned me an empty result. – Kiran RS Jan 15 '14 at 9:42
  • Have you actually replaced your_database_name? – tombom Jan 15 '14 at 9:44
  • yeah sure, i only changed the database name , anything else to change ? – Kiran RS Jan 15 '14 at 9:50
  • Well, I tried it on my machine and it works. Conclusion: Are you sure you actually have tables named "_elggwhatever" in your database? May that be views instead by any chance? – tombom Jan 15 '14 at 9:53
  • I am using MySQL Version-5.5.24. there may be issue of compatibility of the version. – Kiran RS Jan 15 '14 at 9:55
4
SELECT * FROM (
    SELECT CONCAT('DROP TABLE ', GROUP_CONCAT(your_table_name) , ';')
    FROM INFORMATION_SCHEMA.TABLES
    WHERE table_name LIKE 'prefix_%'
) a INTO @mystmt;

PREPARE mystatement FROM @mystmt;
EXECUTE mystatement;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.