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