Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
kenshi_17
Starting Member
2 Posts |
 Posted - 2004-07-20 : 05:03:24
|
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
Master Smack Fu Yak Hacker
1168 Posts |
Posted - 2004-07-20 : 05:05:53
|
| (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! |
 |
|
|
Kristen
Test
22859 Posts |
Posted - 2004-07-20 : 05:28:34
|
| 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 |
|
|
|
Wanderer
Master Smack Fu Yak Hacker
1168 Posts |
Posted - 2004-07-20 : 05:58:54
|
| 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! |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-07-20 : 12:33:15
|
| 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 |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2004-07-20 : 12:52:20
|
| "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 |
|
|
|
kenshi_17
Starting Member
2 Posts |
Posted - 2004-07-20 : 22:21:40
|
| 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!!! |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2004-07-20 : 23:58:42
|
| 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_NAMEFROM INFORMATION_SCHEMA.TABLESWHERE TABLE_TYPE = 'BASE TABLE'ORDER BY TABLE_NAMEOther 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 |
|
|
|
|
|
|
|
|
emptying a database