asked on 7/17/2008

Drop all tables in a database, or just delete the database altogether

Hi,

Is there a sql command to drop all the tables in a database, or just delete the whole database altogether? Right now I'm using:

DROP TABLE my_table_name

which works ok, but it would be easier to drop all of them in one step instead of iterating over every single one. If I just have to drop the whole database, that's fine too, I can just re-create it.

Thanks

Databases SQL

Last Comment

Mark Wills

7/20/2008

Guy Hengel [angelIII / a3]

7/17/2008

you could use this:

exec sp_MsForeachTable ' DROP TABLE ? '

Open in new window

Guy Hengel [angelIII / a3]

7/17/2008

note: above is assuming ms sql server ....

DJ_AM_Juicebox

7/17/2008

ASKER

Oh yeah I was thinking non-database specific stuff - like most databases support a SELECT statement, I was wondering if there were a generic DROP ALL TABLES statement of some sort.

Thanks

Guy Hengel [angelIII / a3]

7/17/2008

sorry, no generic stuff :(

ASKER CERTIFIED SOLUTION

Anthony Perkins

7/19/2008

THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

See Pricing Options

Start Free Trial

Mark Wills

7/20/2008

the DROP DATABASE will remove the instance of all tables, views, procedures and the database. The DROP Tables will leave some elements there.

What is your actual goal ?

DJ_AM_Juicebox

7/20/2008

ASKER

My goal was (for security purposes) see if it was possible to drop a table or the whole database without knowing the database name. If the statements didn't require the DB name or table name, then it would be really easy to mess up someone else's db. It is good that the names are required, at least.

Thanks

Mark Wills

7/20/2008

If you do not have a good / all encompassing security matrix, then it is relatively easy to mess up a database - with out without drop - in fact the more malicious the least obvious it might be... and if there is sufficient privilege to issue a drop, then it is just as easy to get names.