NullPointerExceptional

A blog for .NET hackers

How to Keep Object-dependencies in SQLServer

| Comments

Some time ago I posted about truncating a database. At that point (I guess my SQLServer skills weren’t as advanced as at 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 retrieves all objects in the right order. Nice trick! 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:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41

BEGIN
    DECLARE @schema NVARCHAR(32) = '<your-schema-name-here>'
    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

Comments