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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]