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]
