drop the all tables

Question

Post reply

drop the all tables

HBK_4700

SSCrazy Eights

Points: 8932
More actions
November 3, 2010 at 11:38 am

#149178

Is there an easy way to drop all tables in a database from management studio. I am using SQL 2005

Viewing 15 posts - 1 through 15 (of 22 total)

You must be logged in to reply to this topic. Login to reply

raistlinx Ten Centuries Points: 1345 More actions · Answer 1

SELECT 'DROP TABLE ' + name FROM sys.tables and run the result

homebrew01 SSC Guru Points: 55548 More actions · Answer 2

raistlinx (11/3/2010)
SELECT 'DROP TABLE ' + name FROM sys.tables and run the result

........... After your FULL backup completes

Lowell SSC Guru Points: 323518 More actions · Answer 3

what about this?:

Msg 3726, Level 16, State 1, Line 1
Could not drop object YOURTABLE because it is referenced by a FOREIGN KEY constraint.

you need to drop(or delete/truncate)

in the FK hierarchy order.

here's a script that generates the TRUNCATE/DELETE statements in the right order...simply change to DROP TABLE statements instead.

nocount on

CREATE TABLE #Skipme(TableName varchar(255))

INSERT INTO #Skipme

SELECT 'tbCity' UNION ALL

SELECT 'tbState' UNION ALL

SELECT 'tbCounty' UNION ALL

SELECT 'OtherLookupTables'

INSERT INTO #Skipme

SELECT name from sys.objects

where LEFT(name,2) = 'LU'

OR LEFT(name,2) = 'TB'

declare @level tinyint

set @level = 0

create table #tables (

id int not null primary key clustered,

TableName varchar(255) not null,

Level tinyint not null)

insert into #tables (id, TableName, Level)

select id, '[' + user_name(uid) + '].[' + rtrim(name) + ']' as TableName, 0

from sysobjects where xtype = 'U' and status > 0

while @@rowcount > 0 begin

set @level = @level + 1

update rt set Level = @level

from #tables rt

inner join sysreferences fk on fk.rkeyid = rt.id

inner join #tables ft on ft.id = fk.fkeyid

where ft.Level = @level - 1

end

print 'USE ' + DB_NAME() + '

'

select 'TRUNCATE TABLE ' + TableName

from #tables

where level = 0

And TableName Not In (SELECT TableName from #Skipme)

select 'DELETE ' + TableName

from #tables

where level > 0

And TableName Not In (SELECT TableName from #Skipme)

order by level

drop table #tables

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

WayneS SSC Guru Points: 95460 More actions · Answer 4

laddu4700 (11/3/2010)
Is there an easy way to drop all tables in a database from management studio. I am using SQL 2005

My question is: Why would you want to do this? Doing this will also require you to drop all views, procedures, functions, etc. (especially if any are built with the "WITH SCHEMABINDING" clause). So, why not just start over with a clean database? The only thing that you gain is your security setup, and it would be easier to script that out and start over.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

raistlinx Ten Centuries Points: 1345 More actions · Answer 5

WayneS (11/3/2010)
laddu4700 (11/3/2010)
Is there an easy way to drop all tables in a database from management studio. I am using SQL 2005
My question is: Why would you want to do this? Doing this will also require you to drop all views, procedures, functions, etc. (especially if any are built with the "WITH SCHEMABINDING" clause). So, why not just start over with a clean database? The only thing that you gain is your security setup, and it would be easier to script that out and start over.

Because of the time it takes to create a new clean DB. Often times in developement you may want to clean out the tables quickly to have a fresh empty database, for example testing a conversion procedure. You may save yourself a ton of time by say not having to rebuild a 40Gb db every hour.

Leo.Miller SSChampion Points: 13051 More actions · Answer 6

raistlinx (11/3/2010)
Because of the time it takes to create a new clean DB. Often times in developement you may want to clean out the tables quickly to have a fresh empty database, for example testing a conversion procedure. You may save yourself a ton of time by say not having to rebuild a 40Gb db every hour.

This doesn't make sence. It takes about 20 seconds to rename an exisiting database, and about aminute in MS to create a new empty database. Maybe 3 minutes if you want an initial file size of 40 GB. Why don't you just script the database, drop the whole DB and recreate it empty from the script.

Often when asking questions on the forum it's a good idea to expalain what you want to achieve, i.e. "I want to create an empty db that looks just like my current db, what's the best/fastest way to do this". This way you will normally get a range of good answers and can pick the one that suits you best.

Cheers

Leo

Leo
Nothing in life is ever so complicated that with a little work it can't be made more complicated.

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 7

Or create an empty database one time, make a snapshot of it, run testing, and then restore from the snapshot before you start your next test.

raistlinx Ten Centuries Points: 1345 More actions · Answer 8

Leo.Miller (11/3/2010)
raistlinx (11/3/2010)
Because of the time it takes to create a new clean DB. Often times in developement you may want to clean out the tables quickly to have a fresh empty database, for example testing a conversion procedure. You may save yourself a ton of time by say not having to rebuild a 40Gb db every hour.
This doesn't make sence. It takes about 20 seconds to rename an exisiting database, and about aminute in MS to create a new empty database. Maybe 3 minutes if you want an initial file size of 40 GB. Why don't you just script the database, drop the whole DB and recreate it empty from the script.

Depending on what kind of a RAID environment you may be working in (often beyond your control) it can take a lot longer than that. So let's suppose it took 10 minutes (it does take longer in a RAID setting) and you were testing your conversion... every six times would be an extra hour of work.

The bottom line is that nothing is faster than creating a script once and running it each time you need to clear out your database for a fresh test.

Leo.Miller SSChampion Points: 13051 More actions · Answer 9

raistlinx (11/3/2010)Depending on what kind of a RAID environment you may be working in (often beyond your control) it can take a lot longer than that. So let's suppose it took 10 minutes (it does take longer in a RAID setting) and you were testing your conversion... every six times would be an extra hour of work.
The bottom line is that nothing is faster than creating a script once and running it each time you need to clear out your database for a fresh test.

I'd be interested in seeing real times around this. I recently did an upgrade of 7 SQL 2000 databases to SQL 2005, including doing collation changes on all databases. DBs ranged from 500MB to 80GB, and I used the DROP Database, recreate database from script option to do repeted conversion tests. The percentage time spent recreating empty databases was a small fraction of the over all project time. This on RAID 5, 7200 RPM disks, sharing resources with other databases.

Cheers

Leo

Leo
Nothing in life is ever so complicated that with a little work it can't be made more complicated.

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 10

raistlinx (11/3/2010)
Depending on what kind of a RAID environment you may be working in (often beyond your control) it can take a lot longer than that. So let's suppose it took 10 minutes (it does take longer in a RAID setting) and you were testing your conversion... every six times would be an extra hour of work.

Raid... slower? Than what? A sportscar?

What RAID are you running? How many spindles? Are they dedicated? How did you decide this? Do you have a dead drive in the RAID 5? Did someone leave the parking brake on?

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA

raistlinx Ten Centuries Points: 1345 More actions · Answer 11

Leo.Miller (11/3/2010)
I'd be interested in seeing real times around this. I recently did an upgrade of 7 SQL 2000 databases to SQL 2005, including doing collation changes on all databases. DBs ranged from 500MB to 80GB, and I used the DROP Database, recreate database from script option to do repeted conversion tests. The percentage time spent recreating empty databases was a small fraction of the over all project time. This on RAID 5, 7200 RPM disks, sharing resources with other databases.

I've done similar work and I can tell you without a doubt creating a 40Gb + 20Gb log db takes a lot longer than 3 minutes. But, we are getting a little off on a tangent here. If the time involved to create a db from scratch is acceptable to you then have at it.

But nothing is faster than creating a script once and running it each time you need to clear out your database for a fresh test. Time is money.

The OP asked for a way to easily clear out the tables. It's possible he doesn't have permission to create databases but can create/drop tables. I like to not assume anymore information than given when attempting to answer a question.

raistlinx Ten Centuries Points: 1345 More actions · Answer 12

Craig Farrell (11/3/2010)Raid... slower? Than what?

For writing? Slower than a single drive.

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 13

raistlinx (11/3/2010)
The OP asked for a way to easily clear out the tables.

Nope. Go back and look. The OP asked how to drop all the tables.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

raistlinx Ten Centuries Points: 1345 More actions · Answer 14

Jeff Moden (11/3/2010)
raistlinx (11/3/2010)
The OP asked for a way to easily clear out the tables.
Nope. Go back and look. The OP asked how to drop all the tables.

Yes, bad choice of words in my last post. My initial response was with a method to drop. Thanks for the catch.