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:

dropping tables with dependent ordering
BEGIN
DECLARE @schema NVARCHAR(32) = ''
DECLARE @table NVARCHAR(128)
DECLARE @statement NVARCHAR(128)
DECLARE @dependencies TABLE ( oType SMALLINT , oobjname SYSNAME , oowner NVARCHAR(50) , osequence SMALLINT )
-- load dependecnies
INSERT INTO @dependencies EXEC [sys].[sp_MSdependencies] @objname = NULL
-- filter by oType 8 == Table and given schema
DECLARE TableCursor CURSOR
FORWARD_ONLY
FAST_FORWARD
READ_ONLY
FOR
SELECT [TD].[oobjname]
FROM @dependencies TD
WHERE [TD].[oType] = 8 AND [TD].[oowner] = @schema ORDER BY [TD].[osequence] DESC
OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @table
WHILE @@FETCH_STATUS= 0
BEGIN
SET @statement = N'DROP TABLE [' + @schema + '].[' + @table + ']'
EXEC [sys].[sp_executesql] @statement
FETCH NEXT FROM TableCursor INTO @table
END
CLOSE TableCursor
DEALLOCATE TableCursor
END