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.

No comments:

Post a Comment