drop the all tables

Question

drop the all tables

Viewing 8 posts - 16 through 22 (of 22 total)

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

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

raistlinx (11/3/2010)
Craig Farrell (11/3/2010)Raid... slower? Than what?
For writing? Slower than a single drive.

Sorry, reading back that was a bit rougher sounding than the joke I meant it to be. Thank you for taking that in good humor anyway. And yes, a single drive can write faster than RAID 5. 01 depends on controller traffic.

Noone uses single drive anymore, though, except on local development systems for their branch code, under just about 99% of circumstances, was what I was trying to bring to light (apparently poorly). The rest of the questions I was curious about, because 10 minutes seemed long time. Then I decided to test my own dev environment and found that my own guys have given me crappy spindle usage here, too, for dev, so 10 minutes for 60 gigs... is about right. Ugh.

So to add a little value here:

USE DevDB

GO

-- WARNING: BE VEWY VEWY CAREFUL WITH THIS! It's a db killer.

EXEC sp_msforeachtable 'DROP TABLE ?'

- 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 2

Craig Farrell (11/3/2010) Sorry, reading back that was a bit rougher sounding than the joke I meant it to be. Thank you for taking that in good humor anyway. And yes, a single drive can write faster than RAID 5. 01 depends on controller traffic.

Heh, I did wonder if you were joking. 🙂

And yes I should have clarified I was referring to RAID 5 as the other poster had referred to that specificly.

WayneS SSC Guru Points: 95460 More actions · Answer 3

Hey guys - the reason I asked is two-fold:

1. If the service account running sql server has the "Perform volume maintenance tasks" right, creating even a 1TB db will only take seconds. 40GB is nothing.

2. Ensure ill-will is not intended.

Thus: why drop all tables instead of starting over with a clean database?

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

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

WayneS (11/3/2010)
Thus: why drop all tables instead of starting over with a clean database?

I've got two reasons I've done it in the past, mostly scenario specific though.

1) Dev database, and I don't want to have to script out all the procs/views/etc just to do a data refresh. Though, honestly, I usually use a truncate/reload for this, but occassionally it's easier to do a full on schema refresh.

2) I'm working in 2k and it doesn't like the high volume methods for space, and I don't want to re-zero the entire log that's umpteen gigs on a crappy SAN setup.

EDIT:

*puts on evil DBA hat* Short term need but my other reason:

3)I had a series of developers great for scripting procs/functions, but horrible about schema changes. I would do these daily to make sure they got their schema changes into the change scripts.

- 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

WayneS SSC Guru Points: 95460 More actions · Answer 5

Craig Farrell (11/3/2010)
USE DevDB GO -- WARNING: BE VEWY VEWY CAREFUL WITH THIS! It's a db killer. EXEC sp_msforeachtable 'DROP TABLE ?'

This might need to be run multiple times... you can't drop a table that is a parent in a FK relationship, so you might need to wait until the child has been deleted, then run again. Thus:

WHILE EXISTS (SELECT 1 FROM sys.tables)

EXEC sp_msforeachtable 'DROP TABLE ?'

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

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

WayneS (11/3/2010)

Craig Farrell (11/3/2010)
This might need to be run multiple times... you can't drop a table that is a parent in a FK relationship, so you might need to wait until the child has been deleted, then run again. Thus:
WHILE EXISTS (SELECT 1 FROM sys.tables)
EXEC sp_msforeachtable 'DROP TABLE ?'

Good call. I usually avoid using FK's unless absolutely required (IE: I don't bother with lookup tables for things from dropdowns, etc...) and my application layer is iffy, so I forget about those occassionally.

I guess you could create some kind of sp_msforeachtable DROP ALL FK's type of script to run first, but meh, yours works quite nicely. 🙂

- 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 7

WayneS (11/3/2010)
Hey guys - the reason I asked is two-fold:
1. If the service account running sql server has the "Perform volume maintenance tasks" right, creating even a 1TB db will only take seconds. 40GB is nothing.
2. Ensure ill-will is not intended.
Thus: why drop all tables instead of starting over with a clean database?

No ill -will here 🙂

One little point, I think you are referring to instant file initialization and while that does work for the datafile, it doesn't for the log file. So depending on the size of the log file you need, you sill can be chewing up serveral minutes of time.

If you are only refreshing once or twice a day its a lot different than if you are testing and doing it 6-7 times an hour.

So to turn the question around, if dropping all the tables is faster, why take the extra time to reinstall the db? 😉

Larry Song Grasshopper Points: 22 More actions · Answer 8

Hi Craig , the following script can work well in case of constraints between in tables.

USE DbName

GO

-- Get table name before dropping

SELECT [name] AS TableName FROM SYS.TABLES

-- Drop all tables in database

EXEC sp_msforeachtable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'

EXEC sp_msforeachtable 'ALTER TABLE ? DISABLE TRIGGER ALL'

EXEC sp_msforeachtable 'TURNCATE TABLE ?'

EXEC sp_msforeachtable 'DROP TABLE ?'

-- Get table name after dropping

SELECT [name] AS TableName FROM SYS.TABLES

Craig Farrell (11/3/2010)

WayneS (11/3/2010)
Craig Farrell (11/3/2010)
This might need to be run multiple times... you can't drop a table that is a parent in a FK relationship, so you might need to wait until the child has been deleted, then run again. Thus:
WHILE EXISTS (SELECT 1 FROM sys.tables)
EXEC sp_msforeachtable 'DROP TABLE ?'
Good call. I usually avoid using FK's unless absolutely required (IE: I don't bother with lookup tables for things from dropdowns, etc...) and my application layer is iffy, so I forget about those occassionally.
I guess you could create some kind of sp_msforeachtable DROP ALL FK's type of script to run first, but meh, yours works quite nicely. 🙂