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

2006-10-12 Thread Ow Mun Heng 
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
 
 


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

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

2006-10-11 Thread 方外 醉月 


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  



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

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

2006-10-11 Thread Mark Leith 
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


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

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

2006-10-11 Thread 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


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

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

2006-10-11 Thread 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


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

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

2006-10-11 Thread 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


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

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

2006-10-11 Thread 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


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]