Page 1 of 1

dropping multiple tables

Posted: Thu Feb 19, 2004 5:23 am
by yaron
Hello all,

My wish is a simpel one (i hope).
I need a mysql query to drop multiple tables from my database.
I have a prefix of the name of several tables and I wish to drop all tables that begins with that name.

is this possible?
if not how can I get all tables that begins with that prefix and then delete them one by one?

Thx all

Posted: Thu Feb 19, 2004 5:47 am
by jigaruu
for the three tables 'table1' 'table2' and 'table3' run the following command

DROP TABLE `table1`, `table2`,`table3`;

thats-it.

Posted: Thu Feb 19, 2004 10:03 am
by yaron
hmm... a bit too easy....
As I said I want to drop tables with the same prefix.
i.e. if I have 3 tables t1,t2 and s1
and I want to drop all the tables that begins with a t (assuming I don't know how many there are and what comes after 't')
can it be done?

Posted: Thu Feb 19, 2004 10:18 am
by markl999
You could first "SHOW TABLES LIKE 't%'" then loop over that result set deleting each one.

Posted: Thu Feb 19, 2004 11:08 am
by yaron
thanks.
just what I was looking for :)

Posted: Thu Feb 19, 2004 11:13 am
by yaron
Just a quick question...
When I'm looping by using mysql_fetch_array how do I actually get the table name?

$array['table']????

Posted: Thu Feb 19, 2004 11:24 am
by markl999
while($row = mysql_fetch_array($result)){
echo $row[0]; //this is the table name
}

Posted: Thu Feb 19, 2004 12:15 pm
by yaron
Thanks
Problem solved!!!