Thursday, 10 January 2013

Generating a script in SQL server to delete data from your database

In my previous post, I described how a simple SQL script could be an effective way of resetting your database to a known state between tests in your database integration test suite.

Writing a SQL reset script manually for a large database is tedious though. Imagine having to go through 100 tables and work out what order to delete records in... Fortunately, most database engines expose data about tables and the relationships between them in system tables. You can use this information to derive the dependencies between your tables and automatically generate a sequence of delete statements in the correct order. In Isolating database data in integration tests, Jimmy Bogard demonstrates some C# code that uses the sysobjects and sysforeignkeys tables in SQL Server to do this.

In some cases, you might prefer to have a SQL script that does a similar thing. No code to compile or connection string to configure, just a script that you can copy and paste into SQL Server Management Studio (or your client tool of choice) and execute against your application's database.

The following script analyses the tables and relationships in the current database and generates a sequence of “delete from [table]” statements in the correct order. If it can’t resolve the delete order for all tables, it reports the foreign key constraints that are preventing it from doing so.

Here it is:


Some database schemas may need you to hand-code some parts of your SQL reset script. For example on one project, we had to modify the generated script to accommodate a self referencing constraint:


The number of customisations required will vary from project to project. Hopefully the script will do 90% or more of the work for you.

Why not run the script against your application database and see whether it could help you? I’d be keen to hear from you if you have any comments or ideas.

6 comments:

  1. Very helpful! Out of curiosity, in the commented-out Step 6 code, wouldn't you want to execute the DELETE statements in **descending** depth order (order by Depth DESC) in order to keep from violating the reference constraints?

    ReplyDelete
  2. Do not start to feel demoralized if a couple of contents are turned down, particularly on the off chance that you get criticism about how to enhance your content. You could be effective at your next endeavor to offer a content. screenplay analysis

    ReplyDelete
  3. Such a strikingly basic article.I basically wish to offer a creature proceed for the standard data you have perfect here on this post. lesmeilleursvpn

    ReplyDelete
  4. Thanks so much with this fantastic new web site. IĆ¢��m very fired up to show it to anyone. It makes me so satisfied your vast understanding and wisdom have a new channel for trying into the world. https://internetprivatsphare.at

    ReplyDelete
  5. Always so interesting to visit your site.What a great info, thank you for sharing. this will help me so much in my learning vpnforexpats

    ReplyDelete
  6. I am continually amazed by the amount of information available on this subject. What you presented was well researched and well worded in order to get your stand on this across to all your readers. lemigliorivpn

    ReplyDelete