Re: How can i drop tables with the same predix by using only one single statement?

[Ow Mun Heng]

On Wed, 2006-10-11 at 16:11 +0100, Mark Leith wrote:
> Hey
> 
> 方外 醉月 wrote:
> >
> > How can i do if i want to drop tables with the same predix?

> If you are using 5.0:
> 
> DELIMITER //
> DROP PROCEDURE drop_table_prefix//
> 
> CREATE PROCEDURE drop_table_prefix(IN dbs VARCHAR(64), IN pref VARCHAR(63))
> BEGIN
>   DECLARE done INT DEFAULT 0;
>   DECLARE i INT DEFAULT 0;
>   DECLARE t_name VARCHAR(64);
>   DECLARE cur1 CURSOR FOR SELECT TABLE_NAME 
>                             FROM INFORMATION_SCHEMA.TABLES
>                            WHERE TABLE_SCHEMA = dbs
>                              AND TABLE_NAME LIKE CONCAT(pref,'%');
>   DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
> 
>   OPEN cur1;
> 
>     REPEAT
>       FETCH cur1 INTO t_name;
>       IF NOT done THEN
>         SET @qry = CONCAT('DROP TABLE ', t_name);
>         PREPARE stmt FROM @qry;
>         EXECUTE stmt;
>         DEALLOCATE PREPARE stmt;
>       END IF;
>     UNTIL done END REPEAT;
> 
>   CLOSE cur1;
>   
> END;
> //


This is a good example for me to learn about procedures etc.

Thanks