MySQL : DROP TABLE Starting with a Prefix

Question

EXAMPLE : I have more than 30 tables starting with a prefix "dp_" and about 12 starting with "ex_".

QUESTION : How can I drop all tables starting "dp_" in one query ?

I updated my answer to perform the drop of the tables without using a stored procedure. Give it a Try !!! — RolandoMySQLDBA, Jul 12 '11 at 18:57

RolandoMySQLDBA · Accepted Answer · 2011-07-12T18:55:19.867

Here is a stored procedure that accepts the Database and Prefix String as Parameters:

DELIMITER $$

DROP PROCEDURE DropTablesWithPrefix $$
CREATE PROCEDURE DropTablesWithPrefix(db VARCHAR(64),prfx VARCHAR(20))
StoredProcedure:BEGIN
    DECLARE ndx,maxidx INT;
    DECLARE giventable,SQLSTMT VARCHAR(500);

    CREATE TABLE TableZapList
    (
        droptablesql VARCHAR(512),
        idx INT NOT NULL AUTO_INCREMENT PRIMARY KEY
    ) ENGINE=MyISAM;

    INSERT INTO TableZapList (droptablesql)
    SELECT CONCAT('DROP TABLE ',table_schema,'.',table_name)
    FROM information_schema.tables
    WHERE table_schema = db AND SUBSTR(table_name,LENGTH(prfx)) = prfx;
    SELECT COUNT(1) INTO maxid FROM TableZapList;

    IF maxid = 0 THEN
        LEAVE StoredProcedure;
    END IF;<BR>

    SET ndx = 0;
    WHILE ndx < maxid DO
        SET ndx = ndx + 1;
        SELECT droptablesql INTO SQLSTMT FROM TableZapList WHERE idx = ndx;
        SET @sql = SQLSTMT;
        PREPARE stmt FROM @sql;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;
    END WHILE;<BR>

    SELECT droptablesql FROM TableZapList;
    DROP TABLE TableZapList;
END;

DELIMITER ;

UPDATE 2011-07-12 14:55 EDT

I just thought of a cleaner, simplistic way. Instead of using a stored procedure, simply use the GROUP_CONCAT function to gather all the tables to zap. Then compose into a single query:

Here is a query to drop all tables that start with wp_pol in the current database:

SELECT CONCAT('DROP TABLE ',GROUP_CONCAT(CONCAT(table_schema,'.',table_name)),';')
FROM information_schema.tables
WHERE table_schema=database()
AND table_name like 'wp_pol%';

Next thing to do is store the result of it in

SELECT CONCAT('DROP TABLE ',GROUP_CONCAT(CONCAT(table_schema,'.',table_name)),';')
INTO @dropcmd
FROM information_schema.tables
WHERE table_schema=database()
AND table_name like 'wp_pol%';

Last thing is to execute the dynamic SQL using these three(3) commands:

PREPARE s1 FROM @dropcmd;
EXECUTE s1;
DEALLOCATE PREPARE s1;

Here is a demonstration using MySQL 5.5.12 in Windows that works :

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 11
Server version: 5.5.12 MySQL Community Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

MySQL (Current test) :: use garbage
Database changed
MySQL (Current garbage) :: show tables;
+------------------------------+
| Tables_in_garbage            |
+------------------------------+
| datas                        |
| rolando                      |
| wp_commentmeta               |
| wp_comments                  |
| wp_contact_form_7            |
| wp_links                     |
| wp_most_read_hits            |
| wp_options                   |
| wp_pollsa                    |
| wp_pollsip                   |
| wp_pollsq                    |
| wp_postmeta                  |
| wp_posts                     |
| wp_posts_idtracker           |
| wp_tantan_wordpress_s3_cache |
| wp_term_relationships        |
| wp_term_taxonomy             |
| wp_terms                     |
| wp_usermeta                  |
| wp_users                     |
| wp_w3tc_cdn_queue            |
+------------------------------+
21 rows in set (0.00 sec)

MySQL (Current garbage) :: SELECT CONCAT('DROP TABLE ',GROUP_CONCAT(CONCAT(table_schema,'.',table_name)),';') INTO @dropcmd FROM information_schema.tables WHERE table_schema=database() AND table_name like 'wp_pol%';
Query OK, 1 row affected (0.00 sec)

MySQL (Current garbage) :: SELECT @dropcmd;
+--------------------------------------------------------------------+
| @dropcmd                                                           |
+--------------------------------------------------------------------+
| DROP TABLE garbage.wp_pollsa,garbage.wp_pollsip,garbage.wp_pollsq; |
+--------------------------------------------------------------------+
1 row in set (0.00 sec)

MySQL (Current garbage) :: PREPARE s1 FROM @dropcmd; EXECUTE s1; DEALLOCATE PREPARE s1;
Query OK, 0 rows affected (0.00 sec)
Statement prepared

Query OK, 0 rows affected (0.01 sec)

Query OK, 0 rows affected (0.00 sec)

MySQL (Current garbage) :: show tables;
+------------------------------+
| Tables_in_garbage            |
+------------------------------+
| datas                        |
| rolando                      |
| wp_commentmeta               |
| wp_comments                  |
| wp_contact_form_7            |
| wp_links                     |
| wp_most_read_hits            |
| wp_options                   |
| wp_postmeta                  |
| wp_posts                     |
| wp_posts_idtracker           |
| wp_tantan_wordpress_s3_cache |
| wp_term_relationships        |
| wp_term_taxonomy             |
| wp_terms                     |
| wp_usermeta                  |
| wp_users                     |
| wp_w3tc_cdn_queue            |
+------------------------------+
18 rows in set (0.00 sec)

MySQL (Current garbage) ::

Give it a Try !!!

Rolandos script works but one might need to increase the parameter group_concat_max_len if you a have lot of tables or very long table names: SET SESSION group_concat_max_len = 1000000; — JohnP, Jul 09 '12 at 13:03
Instead of changing group+concat_max_len, I used a subquery with a limit, and performed the drops in steps — pedromanoel, Apr 01 '13 at 13:00
The SUBSTR condition in the INSERT should be: SUBSTR(table_name, 1, LENGTH(prfx)) = prfx then it works fine. — laurent, Dec 04 '15 at 23:38

score 8 · Answer 2 · answered Feb 02 '11 at 10:34

8

Firstly, generate a script to do this at the Unix prompt:

$  echo "select concat('drop table ', table_name, ';') from information_schema.tables where table_name like 'prefix_%';" |mysql --user=root --password=blah --batch >drop.sql

Replace prefix with your own. The --batch option suppresses the fancy formatting MySQL does by default so you can produce a runnable SQL script.

Review the script and if it looks OK, run drop.sql at the mysql> prompt.

answered Feb 02 '11 at 10:34

Gaius

11,200
3
31
64

i've allready done a php helper script to do this , however i was wondering if it can be done using a single query . – poelinca Feb 02 '11 at 11:30
1

Simple answer: No. Complex answer: Yes, if you wrap it in a stored procedure. So you could execute it in one command, but it would be the same as calling a script. – Gaius Feb 02 '11 at 11:46

Marian · Answer 3 · 2011-08-16T05:31:31.287

4

You should query the system tables for those table names and build a string to execute it dynamically. In SQL Server I'd do something like:

declare @x varchar(max), @enter char(2);
select @x = '', @enter = char(13)+char(10);

Select @x = @x + 'drop table ' + table_name + ';' + @enter    
from information_schema.tables    
where table_name like '%accounting%'

print @x
execute (@x);

Now you have to find the corresponding system table in MySQL.

edited Aug 16 '11 at 05:31

answered Feb 02 '11 at 08:20

Marian

15,531
2
60
74

I read your answer very carefully. This is synonymous to my answer. Although in SQL Server dialect, your answer is, in principle, the first correct answer. +1 !!! – RolandoMySQLDBA Aug 16 '11 at 04:46

score 3 · Answer 4 · answered Jun 16 '11 at 23:27

To answer your question, no. Not in MySQL using a single command/query. You'll have to chain commands.

However, if you want to accomplish your goal, here's one way:

From a bash script something like:

#/bin/bash
TABLES=`mysql -s -e "SELECT CONCAT(TABLE_SCHEMA,'.',TABLE_NAME) AS T FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME LIKE 'dp_%'" -u user -p`

for i in $TABLES; 
do 
 echo "DROP TABLE $i;" >> drops.sql ; 
done

cat drops.sql

Then review the drops.sql file. If it's all good, make a BACKUP, then...

mysql -u username -p -v --show-warnings < drops.sql

score 1 · Answer 5 · answered Dec 08 '11 at 20:20

1

You always can use a DATABASE ADMINISTRATOR like navicat and that kind of issues will desapear with a simple select and delete.

answered Dec 08 '11 at 20:20

Nestor Gonzalez

11
1

MySQL : DROP TABLE Starting with a Prefix

5 Answers5

Linked