Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > SQL Server > SQL Server 2005 > SQL Server 2005
Password Reminder
Register
| FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
SQL Server 2005 General discussion of SQL Server *2005* version only.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Server 2005 section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old February 17th, 2007, 11:27 AM
Registered User
 
Join Date: Feb 2007
Location: , , .
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
Default How can empty Database?

Hello
I want to know that how can empty DB?
first , Drop all constraint (primary Key & foreign key) then DELETE all
Record !
Thanks

Reply With Quote
  #2 (permalink)  
Old February 17th, 2007, 11:48 AM
SQLScott's Avatar
Wrox Author
 
Join Date: Dec 2004
Location: Wellington, FL , USA.
Posts: 338
Thanks: 0
Thanked 2 Times in 2 Posts
Default

This could be fairly time consuming based on the number of tables you have.

First, you need to remove all foreign key constraints. Once your foreign key constraints are removed, you can delete all the rows from the tables. So, it would be something like this:

ALTER TABLE tablename DROP CONSTRAINT constraintname
GO

DELETE FROM tablename
GO

You don't need to drop the Primary key to delete rows from a table.



Scott Klein
Author - Professional SQL Server 2005 XML
http://www.wrox.com/WileyCDA/WroxTit...764597922.html
Reply With Quote
  #3 (permalink)  
Old February 18th, 2007, 05:52 AM
Registered User
 
Join Date: Feb 2007
Location: , , .
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Hi scott
I want to perform this action Automatic by "Batch command" .
for example (cursor , Loop , ...)
first , identify all reference Top-Down
then DELETE all of Records Down-Top .
Thank you very much .


Reply With Quote
  #4 (permalink)  
Old February 18th, 2007, 10:32 AM
SQLScott's Avatar
Wrox Author
 
Join Date: Dec 2004
Location: Wellington, FL , USA.
Posts: 338
Thanks: 0
Thanked 2 Times in 2 Posts
Default

Ah, ok, no problem. I'll put something together and send it to you shortly..

Scott Klein
Author - Professional SQL Server 2005 XML
http://www.wrox.com/WileyCDA/WroxTit...764597922.html
Reply With Quote
  #5 (permalink)  
Old February 18th, 2007, 04:45 PM
SQLScott's Avatar
Wrox Author
 
Join Date: Dec 2004
Location: Wellington, FL , USA.
Posts: 338
Thanks: 0
Thanked 2 Times in 2 Posts
Default

So, i think your best bet is to use the system tables and cursors.

Within a cursor, you can query the system tables and for each table, query the system tables to identify all the foreign keys and remove them. Then, delete the data.

This is off the top of my head and have not tested this, but i think it would work. Does this make sense?

If you want an example, i can throw a psuedo-code example together...

Scott Klein
Author - Professional SQL Server 2005 XML
http://www.wrox.com/WileyCDA/WroxTit...764597922.html
Reply With Quote
  #6 (permalink)  
Old February 19th, 2007, 08:36 AM
Registered User
 
Join Date: Feb 2007
Location: , , .
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Hello Scott
I solve this conundrum .
thank to expend your time for help me .

Reply With Quote
  #7 (permalink)  
Old February 19th, 2007, 09:12 AM
Friend of Wrox
Points: 1,536, Level: 15
Points: 1,536, Level: 15 Points: 1,536, Level: 15 Points: 1,536, Level: 15
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Oct 2006
Location: , MI, USA.
Posts: 475
Thanks: 0
Thanked 9 Times in 9 Posts
Default

Morik,

How did you solve it, please?

--Jeff Moden
Reply With Quote
  #8 (permalink)  
Old February 25th, 2007, 05:02 AM
Registered User
 
Join Date: Feb 2007
Location: , , .
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Jeff
sorry for delay .
contact me with (morik.aryan@gmail.com),please .


Reply With Quote
  #9 (permalink)  
Old March 2nd, 2007, 01:56 PM
Friend of Wrox
 
Join Date: Aug 2004
Location: Orange County, CA, USA.
Posts: 385
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Another possibly easier route is to query the system tables and create a script that will create all tables without any records or constraints. Then drop the database, re-create the database and apply the script that creates all tables.

Reply With Quote
  #10 (permalink)  
Old March 6th, 2007, 02:07 PM
Registered User
 
Join Date: Feb 2007
Location: , , .
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
Default

-- Persian Boy
Hello
do you really think that a DATABASE only made of TABLE?????????????
convenience !
Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Inner empty tag jdzsolt XSLT 2 August 8th, 2008 03:49 PM
Query was empty!!!!!!!!!!!!!!!!!!!!!!!!! gargamel BOOK: Beginning PHP5, Apache, and MySQL Web Development ISBN: 978-0-7645-7966-0 5 March 27th, 2007 11:32 AM
check if empty hosefo81 Javascript How-To 12 March 9th, 2004 08:43 AM
if search comes up empty abbylee26 Classic ASP Databases 3 October 4th, 2003 04:45 AM



All times are GMT -4. The time now is 05:03 PM.


Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.