Some time ago I posted about truncating a database. At that point (I guess my SQLServer skills weren’t as advanced as nowadays - which doesn’t mean they are quite well now …) I thought this was a good solution - googling leads often to the same solution.
But getting older and gray in the head I realized there is a much better way to do: sp_MSdependencies one of several undocumented stored procedures of the SQLServer.
The advantage of sp_MSdependecies is, that it retrieves all dependent objects in a hierarchical list (expressed by the osequence). If you fill NULL on the objname parameter it will retrieve all objects in the correct dependent order. Nice one though!
So what I did is to load the data into a temp table and sort it according to its osequence to build the correct order. Remember: Deleting tables with foreign-keys needs to have a different order than deleting data from it.
I use the following snippet from time to time and want to share with you: