9

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!

3 Answers 3

13

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';"`
6
  • first step returned me an empty result.
    – Kiran RS
    Commented Jan 15, 2014 at 9:42
  • Have you actually replaced your_database_name?
    – tombom
    Commented Jan 15, 2014 at 9:44
  • yeah sure, i only changed the database name , anything else to change ?
    – Kiran RS
    Commented Jan 15, 2014 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
    Commented Jan 15, 2014 at 9:53
  • I am using MySQL Version-5.5.24. there may be issue of compatibility of the version.
    – Kiran RS
    Commented Jan 15, 2014 at 9:55
4
SELECT * FROM (
  SELECT CONCAT('DROP TABLE ', GROUP_CONCAT(table_name) , ';')
  FROM INFORMATION_SCHEMA.TABLES
  WHERE table_name LIKE 'prefix_%'
) a INTO @mystmt;

PREPARE mystatement FROM @mystmt;
EXECUTE mystatement;
0
2

Use the following query:

-- Set up variable to delete ALL tables starting with 'temp_'
SET GROUP_CONCAT_MAX_LEN=10000;
SET @tbls = (SELECT GROUP_CONCAT(TABLE_NAME)
               FROM information_schema.TABLES
              WHERE TABLE_SCHEMA = 'my_database'
                AND TABLE_NAME LIKE 'temp_%');
SET @delStmt = CONCAT('DROP TABLE ',  @tbls);
-- SELECT @delStmt;
PREPARE stmt FROM @delStmt;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

You need to change at TABLE_SCHEMA and TABLE_NAME appropriately.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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