hether you develop custom database solutions or administer existing databases, you know that deleting records is a common task that's easy to automatemost of the time. However, deleting all
the records in a SQL Server database can be a bit more challenging because a built-in safeguard prevents you from deleting related data. To work around this protection, you must know your data well and work through the tables manually in just the right order. Otherwise, you may violate constraints.
If you're automating the deletion process for the first time, you might consider Transact-SQL's DELETE or TRUNCATE commands, as both delete all the records from a table (with some limitations). If a constraint exists between the data in two or more tables, however, a DELETE or TRUNCATE will fail. In addition, both commands work with just one table at a time. If you want to delete all of the records from all of the tables in a SQL Server database while avoiding the constraint limitation, you have two options:
- Delete records in an orderly fashion, taking constraints into consideration. This solution is impractical in a database with many tables. Even in a database with just a few tables, you'll waste time trying to determine the constraints and getting the order right the first, second, or even third time.
- Disable all the constraints, delete all the data, and then enable the constraints. This renders all the work of Option 1 unnecessary, because a simple stored procedure script can whip through the task in no time.
If the delete task is a one-time event and you have just a few tables, you may choose Option 1. On the other hand, if you have the stored procedure for Option 2 handy, it will work faster than any manual effort, regardless of how well you know your data.
Option 2: The Easy Way
You undoubtedly will find many solutions for deleting all the data in a database on the Internet and in other publications, but most of those solutions are rather convoluted. Purists will claim that there's a right way and a wrong way to delete all your data, but I say there's an easy way and a hard way. The easy way is to simply disable all the constraints in the database, delete all the data, and then restore the constraints.
The first thing you need is code that loops through all the tables. I recommend using sp_MSForEachTable, which makes looping through all the tables in a database easy. Because sp_MSForEachTable is an undocumented stored procedure, some developers refuse to work with it. That's why so many solutions for comprehensive deleting involve complex code. You certainly should avoid some undocumented commands, but not this one. It's a great tool for this job. The only catch is those constraints still rear their ugly heads, which means the following statement won't work as you might expect:
EXEC sp_MSForEachTable 'DELETE FROM ?'
DELETE is still unable to remove related data (technically, it can't delete data that's connected by a constraint). While it may be frustrating in this case, that behavior is built in to protect your data.
|Author's Note: The above statement, EXEC sp_MSForEachTable 'DELETE FROM ?', will work on lone tables such as lookup tables. So be careful because if there's no constraint, the statement will delete data.|
What you need is code that first removes constraints, then deletes the data, and finally restores the constraints:
CREATE PROCEDURE sp_DeleteAllData
EXEC sp_MSForEachTable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'
EXEC sp_MSForEachTable 'ALTER TABLE ? DISABLE TRIGGER ALL'
EXEC sp_MSForEachTable 'DELETE FROM ?'
EXEC sp_MSForEachTable 'ALTER TABLE ? CHECK CONSTRAINT ALL'
EXEC sp_MSForEachTable 'ALTER TABLE ? ENABLE TRIGGER ALL'
EXEC sp_MSFOREACHTABLE 'SELECT * FROM ?'
The first two statements disable constraints and triggers. The third statement actually deletes all the data. The next two statements restore the constraints and triggers. The last statement can show records from each table in the database. You can delete this last statement if you like, as it isn't critical to the technique; it merely verifies, visually, that each table is empty.
Using SQL Server Management Studio, you can quickly add this stored procedure to a specific database (as shown in Figure 1) or to a model (so that it's available to all new databases). Just use EXEC to call the new stored procedure. Even in a large database, it doesn't take long to complete a deletion.
Figure 2 shows the result of running sp_DeleteAllData against a backup copy (SalesCopy) of a database named Sales, which is a bunch of empty tables.
Figure 1. Create the Stored Procedure: You can quickly add the stored procedure to a specific database.
Figure 2. The Result of Running sp_DeleteAllData Against a DB: This stored procedure disables constraints and triggers before deleting data.