SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 MSDE (2000)
 emptying a database
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

kenshi_17
Starting Member

2 Posts

Posted - 07/20/2004 :  05:03:24  Show Profile  Reply with Quote
hi! how can i completely empty a databse? I'm using MSDE. And, how and where can i query in MSDE?

All replies are appreciated... thank you very much

Wanderer
Flowing Fount of Yak Knowledge

United Kingdom
1167 Posts

Posted - 07/20/2004 :  05:05:53  Show Profile  Reply with Quote
(1) - script out all the objects, and recreate the database

(2) truncate all tables - you will need to do this in a child to parent order, which could be *fun* in a highly related database.

As to how and where can you query MSDE ... using Query Analyzer, I would say, and T-SQL. Is that what you are asking?

cIaO

*##* *##* *##* *##*

Chaos, Disorder and Panic ... my work is done here!
Go to Top of Page

Kristen
Test

United Kingdom
22415 Posts

Posted - 07/20/2004 :  05:28:34  Show Profile  Reply with Quote
Sorry, Wanderer, no truncate permitted on tables with FKs (even if the child tables are empty) :-(

So you'd have to drop the RI, TRUNCATE TABLES and then recreate RI.

Derrick (or maybe Brett) was suffering this job a few weeks back and scripted it I think - perhaps they can post a link here for kenshi_17?

Kristen
Go to Top of Page

Wanderer
Flowing Fount of Yak Knowledge

United Kingdom
1167 Posts

Posted - 07/20/2004 :  05:58:54  Show Profile  Reply with Quote
Grrr ... would have to use Unconditional deletes then , which would be a *bugger* on a large DB.

As I recall, a DTS script will NOT include indexes, so bear that in mind if you think about going that route, Kenshi

*##* *##* *##* *##*

Chaos, Disorder and Panic ... my work is done here!

Edited by - Wanderer on 07/20/2004 05:59:50
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37143 Posts

Posted - 07/20/2004 :  12:33:15  Show Profile  Visit tkizer's Homepage  Reply with Quote
To query in MSDE, you must do it at the command line using osql. You can only use the SQL Client Tools if you are licensed to use them. You can get a developer's license for 49 bucks. That's pretty cheap considering the headaches you'll get from osql if you aren't familiar with command line tools.

Tara
Go to Top of Page

Kristen
Test

United Kingdom
22415 Posts

Posted - 07/20/2004 :  12:52:20  Show Profile  Reply with Quote
"That's pretty cheap considering the headaches you'll get from osql if you aren't familiar with command line tools"

That's a modest little statement for a Bear Trap!

Kristen
Go to Top of Page

kenshi_17
Starting Member

2 Posts

Posted - 07/20/2004 :  22:21:40  Show Profile  Reply with Quote
thanks guys/gals for the replies... hhmmm but i kinda still don't know how... i've thought about truncating tables one by one, but on second thought... the database i'm studying or i may say should understand well has a lot of tables in it.

follow-up questions(if all of you don't mind, i hope so...)
1. I'm using VB ASP.Net-MSDE(as my back end). I'm planning of just making a program for the purpose of deleting all the data in the database. Or if probable, is there already a program performing it?
2. What is the procedure in performing all the necessary functions in SQL statements in MSDE? Should I use the command prompt? how? (sorry guys for the inconvenience, I'm kinda new in MSDE).

All replies, again would be appreciated. thanks a lot!!!
Go to Top of Page

Kristen
Test

United Kingdom
22415 Posts

Posted - 07/20/2004 :  23:58:42  Show Profile  Reply with Quote
The following will give you a list of tables which you can use from VB (i.e. loop round) to do a "TRUNCATE TABLE xxx" (if you have NO foreign keys) otherwise a "DELETE FROM xxx"

SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
ORDER BY TABLE_NAME

Other than a SQL script run from Quary Analyser, or OSQL (command prompt) there isn't anything built in.

You could have an "empty database" that you just RESTORE or ATTACH to replace the existing database for a "fresh start"

2. If you have a licence you can use SQL Client Tools (Enterprise Manager or Query Analyser) to do these jobs, otherwise you are stuck with OSQL or rolling your own in VB.

I think you could use a developer licence for the client tools, which costs about US$ 50.

Microsoft have an ASP (.NET IIRC) management toolkit for SQL, but I haven't used it and don't know if its free or something else!

Kristen
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000