Have you ever been in a scenario where you have to clear down some data within a database that is chock full of constraints but don't want to wipe out your precious relationships, indices and all that other jazz?
I found myself in a similar situation earlier this week, and needed a clear-down script that would wipe out all of the data within an entire database, without being bothered by any existing constraints. Here it is.
- USE @YourTable;
- EXEC sp_MSForEachTable "ALTER TABLE ? NOCHECK CONSTRAINT ALL"
- EXEC sp_MSForEachTable "DELETE FROM ?"
- EXEC sp_MSForEachTable "ALTER TABLE ? WITH CHECK CHECK CONSTRAINT ALL"
- GO
What is this doing?
The script itself takes advantage of an undocumented stored procedure within SQL Server called sp_MSForEachTable that will actually iterate through all of the tables within a given database.
Now that we know we are going to be looping through each of the tables within the specified database, let's see what is going to happen to each of the tables.
- ALTER TABLE ? NOCHECK CONSTRAINT ALL
This will disable any constraint checking that is present on the table (so, operations like deleting a primary key or a related object won't trigger any errors).
- DELETE FROM ?
This will delete every record within the table.
- ALTER TABLE ? WITH CHECK CHECK CONSTRAINT ALL
This re-enables the constraint checking, bringing your table back to its original state, sans data.
Note
It is very important that you properly scope this query to the table that you are targeting to avoid any crazy data loss.
While I don't think that you could just leave that out and execute on master, I wouldn't want to even risk testing that out (although feel free to try it out and let me know if it nukes everything).