SEO
vBulletin Search Engine Optimization
| |||||||
| Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read |
02-27-2008, 09:31 PM
| ||||
| ||||
 How can i drop tables with the same predix by using only one single statement? How can i do if i want to drop tables with the same predix? For example, there is a database including a lot of tables,such as tableA,tableB....(table*) Now,how can i drop those tables by using only one single statement? __________________________________________________ _______________ 与联机的朋友进行交流，请使用 MSN Messenger: http://messenger.msn.com/cn      |
|
02-27-2008, 09:31 PM
| |||
| |||
| Re: How can i drop tables with the same predix by using only onesingle statement? Hey 方外 醉月 wrote: > > How can i do if i want to drop tables with the same predix? > > For example, there is a database including a lot of tables,such as > tableA,tableB....(table*) > > Now,how can i drop those tables by using only one single statement? > > __________________________________________________ _______________ > 与联机的朋友进行交流，请使用 MSN Messenger: http://messenger.msn.com/cn > 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; // An example: mysql> SHOW TABLES; +----------------+ | Tables_in_test | +----------------+ | a1 | | a2 | | t1 | | t2 | +----------------+ 4 rows in set (0.00 sec) mysql> CALL drop_table_prefix('test', 't')// Query OK, 0 rows affected (0.01 sec) mysql> SHOW TABLES// +----------------+ | Tables_in_test | +----------------+ | a1 | | a2 | +----------------+ 2 rows in set (0.00 sec) Hope this helps. Mark -- Mark Leith, Support Engineer MySQL AB, Worcester, England, www.mysql.com Are you MySQL certified? www.mysql.com/certification |
|
02-27-2008, 09:32 PM
| |||
| |||
| Re: How can i drop tables with the same predix by using only onesingle statement? 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 |
|
02-27-2008, 09:32 PM
| |||
| |||
| Re: How can i drop tables with the same predix by using only onesingle statement? 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 |
|
02-27-2008, 09:32 PM
| |||
| |||
| Re: How can i drop tables with the same predix by using only onesingle statement? 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 |
|
02-27-2008, 09:32 PM
| |||
| |||
| Re: How can i drop tables with the same predix by using only onesingle statement? 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 |
|
02-27-2008, 09:32 PM
| ||||
| ||||
| Re: How can i drop tables with the same predix by using only onesingle statement? Sorry for the multiple post screw up. My Bad On Thu, 2006-10-12 at 11:43 +0800, Ow Mun Heng wrote: > 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 > > |
Linear Mode
