• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Clean database

 
avihai marchiano
Ranch Hand
Posts: 342
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hey,

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
Sheriff
Pie
Posts: 20512
54
Chrome Eclipse IDE Java Windows
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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:

 
avihai marchiano
Ranch Hand
Posts: 342
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Too much complicated.

There is no tool or command to get the order?
 
Jeanne Boyarsky
author & internet detective
Marshal
Posts: 34210
341
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Avihai,
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)
 
Paul Campbell
Ranch Hand
Posts: 338
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Trunc table
 
David O'Meara
Rancher
Posts: 13459
Android Eclipse IDE Ubuntu
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Connect to DB using Squirrel SQL, select all tables, right-click, delete records
 
Paul Sturrock
Bartender
Posts: 10336
Eclipse IDE Hibernate Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Drop the database and restore from a backup, excluding data?
 
avihai marchiano
Ranch Hand
Posts: 342
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thanks a lot for your answers , i thought that can be an easier way.

Just a sample command.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic