Technical Article

Stored Procedure Call Hierarchy


Easily extractss the complete stored procedure call hierarchy from "sysdepends" table of SQL Server and represent it in a pseudo-graphical tree-view.

The script uses a temporary table (automatically created/dropped) to hold children and parents of relationships. For representing the call hierarchy the table contains a level field (depth) and a field containing the "enumerated path" (using the procedure's id) denoting the hierarchical position of the respective child procedure (similar to chapter-numbering in a book).

First of all the script inserts all top-level procedures into the table, then all parent-child relations (both extracted from "sysdepends" table). A loop calculates the "enumerated path". It uses as many iterations as necessary to process all relationships in the table.

If there are cyclic dependencies the script will also detect that (number of dependencies that cannot be calculated does not change between two iterations). The two select statements at the end output the result.

Script has been successfully tested in larger environments (2K stored procedures) showing quite fine performance. Analysis capabilities are powerful (eg. selecting subtrees is not an issue, give it a try).

-- Generate temporary table to hold procedure call tree
IF OBJECT_ID('tempdb..#procdeps') IS NOT NULL DROP TABLE #procdeps
CREATE TABLE #procdeps (id_child INT, name_child NVARCHAR(128), id_parent INT, name_parent NVARCHAR(128), level INT, hierarchy VARCHAR(900))
ALTER TABLE #procdeps ADD CONSTRAINT uk_child_parent UNIQUE (id_child, id_parent)
CREATE INDEX idx_hierarchy ON #procdeps (hierarchy)

   DECLARE @proccnt INT
   DECLARE @prevcnt INT
   DECLARE @itercnt INT

   TRUNCATE TABLE #procdeps

   -- Insert all top level procedures from sysdepends into tree table (all that are not listed as children of relationships)
   -- Initialize level and path (needed for calculating relationships afterwards)
   PRINT 'Generating procedure tree ... ' + CHAR(13) + CHAR(10) + 'Inserting top level procedures ...'
   INSERT INTO #procdeps
   SELECT,, NULL, NULL, 0, '.' + CAST( AS VARCHAR) + '.'
     FROM sysobjects obj
    WHERE obj.xtype = 'P'
      AND OBJECTPROPERTY(, 'ismsshipped') = 0
      AND NOT IN (
          SELECT depid
            FROM sysdepends)

   -- Insert all dependent procedures into tree table
   PRINT 'Inserting dependent procedures ...'
   INSERT INTO #procdeps
     FROM sysobjects obj1,
          sysobjects obj2,
          sysdepends dep
    WHERE =
      AND obj1.xtype = 'P'
      AND OBJECTPROPERTY(, 'ismsshipped') = 0
      AND = dep.depid
      AND obj2.xtype = 'P'
      AND OBJECTPROPERTY(, 'ismsshipped') = 0

   -- Repeat until all relationships are calculated (or a cycle is detected)
   PRINT 'Calculating relationships ...'
   SET @itercnt = 0
   SET @prevcnt = 0
   SELECT @proccnt = COUNT(1) FROM #procdeps WHERE hierarchy IS NULL

   WHILE @proccnt > 0 AND @prevcnt <> @proccnt BEGIN   -- Run 10 iterations at max
      PRINT 'Iteration ' + CAST(@itercnt + 1 AS VARCHAR) + ' - ' + CAST(@proccnt AS VARCHAR) + ' Dependencies to calculate ...'

      -- Node gets level of parent + 1 (top level node gets 0)
      -- Node appends its id to path of parent (all ids delimited by dots, top level node gets just its id)
      -- Top level case is not needed here (only used if statement should calculate dependency for single rows iteratively)
      UPDATE child
         SET level = CASE
                WHEN child.id_parent IS NULL THEN 0
                ELSE parent.level + 1
             hierarchy = CASE
                WHEN child.id_parent IS NULL THEN '.'
                ELSE parent.hierarchy
             END + CAST(child.id_child AS VARCHAR) + '.'
        FROM #procdeps child LEFT OUTER JOIN
             #procdeps parent ON child.id_parent = parent.id_child

      -- Count iteration and check if missing procedures
      -- If count of procedures without hierarchy does not change between iterations a cycle is detected
      SET @prevcnt = @proccnt
      SET @itercnt = @itercnt + 1
      SELECT @proccnt = COUNT(1) FROM #procdeps WHERE hierarchy IS NULL

   IF @proccnt = @prevcnt
      PRINT 'Finished (cycles detected) ...'
      PRINT 'Finished ...'
   PRINT CHAR(13) + CHAR(10)

-- Select hierarchical dependencies as pseudo graphical tree view
PRINT 'Procedure hierarchy ...'
          WHEN level = 0 THEN name_child
          ELSE REPLICATE(' | ', level) + name_child
       END AS NVARCHAR(256)) proctree
  FROM #procdeps
 WHERE hierarchy IS NOT NULL
 ORDER BY hierarchy

-- Select procedures with cyclic call graph
PRINT 'Cyclic dependencies ...'
SELECT CAST(name_child + ' -> ' + name_parent AS NVARCHAR(256)) proctree
  FROM #procdeps
 WHERE hierarchy IS NULL
 ORDER BY hierarchy


5 (5)

You rated this post out of 5. Change rating




5 (5)

You rated this post out of 5. Change rating