Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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 ?

share|improve this question
    
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

5 Answers 5

up vote 9 down vote accepted

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 !!!

share|improve this answer
    
i'll review it and give it a try and let everibody know how it whent . thanks –  poelinca Feb 21 '11 at 7:12
    
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 9 '12 at 13:03
    
@JohnP Good point. I always take it for granted because I set it to 5000000 for all my clients. I never think about, so it is definitely worth mentioning at this time. Thank You !!! –  RolandoMySQLDBA Jul 9 '12 at 13:06
    
Instead of changing group+concat_max_len, I used a subquery with a limit, and performed the drops in steps –  pedromanoel Apr 1 '13 at 13:00

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.

share|improve this answer
    
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 2 '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 2 '11 at 11:46

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.

share|improve this answer
    
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 4:46

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
share|improve this answer

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

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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