File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
The moose likes JDBC and Relational Databases and the fly likes Clean database Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC and Relational Databases
Bookmark "Clean database " Watch "Clean database " New topic

Clean database

avihai marchiano
Ranch Hand

Joined: Jan 10, 2007
Posts: 342

is there a way (i dont mind to use tool) to clean the DB.
Dont delete the tables, only delete the content.

if i do it one by one i failed with constraints error.

can i some how get the order or have a workaround or tool that will do it for me?

Thank you
Rob Spoor

Joined: Oct 27, 2005
Posts: 20271

As long as there are no circular references, you can find the order by trying. By following the next approach you'll set up several (overlapping) tree structures, which you can then delete from root to leaf.

If a table can be cleared (DELETE FROM tablename), then it is a root node. If it is not, you should be able to read from the error which table(s) depend(s) on that table, and those tables will be the parents of the current table. You'll just continue until a table can be deleted without problems.

You could automatically build these trees, if you can determine from the error messages which table you should try to delete next.

Alternatively, you can store all tables in a collection, and keep trying to clear one at a time, removing all successfully cleared tables, until either nothing can be removed (circular references) or the collection is empty:

How To Ask Questions How To Answer Questions
avihai marchiano
Ranch Hand

Joined: Jan 10, 2007
Posts: 342
Too much complicated.

There is no tool or command to get the order?
Jeanne Boyarsky
author & internet detective

Joined: May 26, 2003
Posts: 33102

Another approach is to:
1) Disable all constraints (This can be done through SQL - the command varies by database)
2) delete data from all tables
3) Re-enable all constraints (mirror image of the sql from step 1)

[OCA 8 book] [Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Other Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, TOGAF part 1 and part 2
Paul Campbell
Ranch Hand

Joined: Oct 06, 2007
Posts: 338
Trunc table
David O'Meara

Joined: Mar 06, 2001
Posts: 13459

Connect to DB using Squirrel SQL, select all tables, right-click, delete records
Paul Sturrock

Joined: Apr 14, 2004
Posts: 10336

Drop the database and restore from a backup, excluding data?

JavaRanch FAQ HowToAskQuestionsOnJavaRanch
avihai marchiano
Ranch Hand

Joined: Jan 10, 2007
Posts: 342
Thanks a lot for your answers , i thought that can be an easier way.

Just a sample command.
I agree. Here's the link:
subject: Clean database
It's not a secret anymore!