/    Sign up×
Community /Pin to ProfileBookmark

Drop all tables

Copy linkTweet thisAlerts:
Nov 11.2004

Hi

Is there SQL syntax or php coding to drop all tables in a database

There maybe a range of different tables so I cant name them all as sometimes they may not be there but can I just drop all tables in a database and if so how???

Many thanks
Adam

to post a comment
PHP

18 Comments(s) ↴

Copy linkTweet thisAlerts:
@96turnerriNov 11.2004 — well the SYNTAX for DROP is
[code=php]DROP TABLE table_name[/code]
and since
[code=php]SELECT * FROM[/code] is the same as [code=php]SELECT colomn1, the rest in here lastcolomn FROM[/code]

so instead of [code=php]DROP TABLE first_table, the rest in here last_table[/code] you could use [code=php]DROP TABLE *[/code] would work, however that is untested
Copy linkTweet thisAlerts:
@k0r54authorNov 11.2004 — Hi,

Thanks but i tried that first ?

didn't work unfortunatly
Copy linkTweet thisAlerts:
@96turnerriNov 11.2004 — ok, well these are just guesses but im using SQL logic

DROP *

TRUNCATE TABLE *

TRUNCATE *

you could always use SHOW TABLES and then for each one do DROP TABLE, this would definatly work
Copy linkTweet thisAlerts:
@k0r54authorNov 11.2004 — ok great i will try that!

If any1 has a single sql command that would be gr8

cheers
Copy linkTweet thisAlerts:
@Paul_JrNov 11.2004 — [font=courier]DROP DATABASE db_name[/font] will drop all tables from [i]db_name[/i] and delete the database.
Copy linkTweet thisAlerts:
@96turnerriNov 11.2004 — i was presuming he wanted to keep the db' and its structure so i didnt suggest that
Copy linkTweet thisAlerts:
@k0r54authorNov 11.2004 — Can I then create a database in php though?
Copy linkTweet thisAlerts:
@Paul_JrNov 11.2004 — Well then, I don't think he can drop all tables without specifying each table.

[i]Originally posted by k0r54 [/i]

[B]Can I then create a database in php though? [/B][/QUOTE]
Yes, with [url=http://www.php.net/mysql-create-db]mysql_create_db()[/url]
Copy linkTweet thisAlerts:
@96turnerriNov 11.2004 — thats why i suggest SHOW TABLES and THEN for EACH drop or build the query using SHOW TABLES then its only executing one SQL command
Copy linkTweet thisAlerts:
@k0r54authorNov 11.2004 — ok

How would i do that ?? and there will be ABOUT 20 tables what will the process time be like?

Thanks Adam
Copy linkTweet thisAlerts:
@k0r54authorNov 12.2004 — Hi,

you mentioned about the show tables then drop them

how would i do that exacly!

so far i have

[code=php]
$show = "SHOW TABLES";
$show_res = mysql_query($show,$conn) or die(mysql_error());

foreach ???
[/code]

Thanks

Adam
Copy linkTweet thisAlerts:
@96turnerriNov 12.2004 — try this

[code=php]
$num = 0;
$show = "SHOW TABLES";
$show_res = mysql_query($show,$conn) or die(mysql_error());
while($row = mysql_fetch_assoc($show_res)) {
$sql = "DROP TABLE '".$row[$num]."'";
mysql_query($sql) or die(mysql_error());
$num++;
}
[/code]
Copy linkTweet thisAlerts:
@KeveenNov 12.2004 — I have had the same problem, trying to drop all hte tables in a database while testing and transferring web sites to different servers. I could not find a command, it seems to be one of those obvious common sense requirements that somehow got left out of the command programming. I'm not a programmer so most of the time I'm using one of the front ends and MySQL 4 thankfully has an option to select all tables after which you select the command DROP and the database is emptied, ready to import a new or backed up sql backup file. Can't imagine life without some kind of graphic front end!
Copy linkTweet thisAlerts:
@96turnerriNov 12.2004 — [i]Originally posted by Keveen [/i]

[B]MySQL 4 thankfully has an option to select all tables after which you select the command DROP and the database is emptied[/B][/QUOTE]

dont you mean PHPMyAdmin ?
Copy linkTweet thisAlerts:
@KeveenNov 12.2004 — Yep, sure do - that's what happens when I have my head in a computer all day!
Copy linkTweet thisAlerts:
@usikJan 30.2007 — DROP * FROM 'database_name'

is the syntax ?
Copy linkTweet thisAlerts:
@NightShift58Jan 30.2007 — DROP * FROM 'database_name' is the syntax[/quote]No. Thankfully, the syntax is:[code=php]DROP [TEMPORARY] TABLE [IF EXISTS]
tbl_name [, tbl_name] ...
[RESTRICT | CASCADE][/code]See: http://dev.mysql.com/doc/refman/5.0/en/drop-table.html

Anyting like usik's statement yields:Error Code : 1064
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '* FROM nightshift58 at line 1
Copy linkTweet thisAlerts:
@usikJan 30.2007 — sorry i for got to mention my code was meant for phpmyadmin
×

Success!

Help @k0r54 spread the word by sharing this article on Twitter...

Tweet This
Sign in
Forgot password?
Sign in with TwitchSign in with GithubCreate Account
about: ({
version: 0.1.9 — BETA 5.7,
whats_new: community page,
up_next: more Davinci•003 tasks,
coming_soon: events calendar,
social: @webDeveloperHQ
});

legal: ({
terms: of use,
privacy: policy
});
changelog: (
version: 0.1.9,
notes: added community page

version: 0.1.8,
notes: added Davinci•003

version: 0.1.7,
notes: upvote answers to bounties

version: 0.1.6,
notes: article editor refresh
)...
recent_tips: (
tipper: @AriseFacilitySolutions09,
tipped: article
amount: 1000 SATS,

tipper: @Yussuf4331,
tipped: article
amount: 1000 SATS,

tipper: @darkwebsites540,
tipped: article
amount: 10 SATS,
)...