T-SQL Puzzler - Crawling Object Dependencies

Question

This code involves a recursive Stored Procedure call and a "not so great" method of avoiding cursor name collision. In the end I don't care if it uses cursors or not. Just looking for the most elegant approach. I'm mainly going to use it as a simple method to track down Stored Proc hierarchies (without buying a product). I tried cursors within "dynamic sql" and didn't have much luck. I'd like to go about 10 levels deep.

The desired output:

sp_Master_Proc_Name  
-- sp_Child_Proc_1_Name  
---- sp_Sub_Proc_1_Name    
-- sp_Child_Proc_2_Name  
-- sp_Child_Proc_3_Name

Its not pretty, but here is the code (and it didn't work as expected)

    CREATE PROCEDURE SP_GET_DEPENDENCIES
 (
   @obj_name varchar(300),
   @level int
 )
 AS
 DECLARE @sub_obj_name varchar(300)
 IF @level = 1
   BEGIN
  PRINT @obj_name
   END

 IF @level = 1
   BEGIN 
  DECLARE the_cursor_1 CURSOR FOR 
   SELECT DISTINCT REPLICATE('--', @level) + ' ' + c.name FROM dbo.sysdepends a
     INNER JOIN dbo.sysobjects b ON a.id = b.id
     INNER JOIN dbo.sysobjects c ON a.depid = c.id
     WHERE b.name = @obj_name
  OPEN the_cursor_1
  SET @level = @level + 1
  FETCH NEXT FROM the_cursor_1 INTO @sub_obj_name 
  WHILE @@FETCH_STATUS = 0 
    BEGIN 
   PRINT @sub_obj_name
   EXEC SP_GET_DEPENDENCIES @sub_obj_name, @level 
   FETCH NEXT FROM the_cursor_1 INTO @sub_obj_name 
    END
  CLOSE the_cursor_1
  DEALLOCATE the_cursor_1
   END

 IF @level = 2
   BEGIN 
  DECLARE the_cursor_2 CURSOR FOR 
   SELECT DISTINCT REPLICATE('--', @level) + ' ' + c.name FROM dbo.sysdepends a
     INNER JOIN dbo.sysobjects b ON a.id = b.id
     INNER JOIN dbo.sysobjects c ON a.depid = c.id
     WHERE b.name = @obj_name
  OPEN the_cursor_2
  SET @level = @level + 1
  FETCH NEXT FROM the_cursor_2 INTO @sub_obj_name 
  WHILE @@FETCH_STATUS = 0 
    BEGIN 
   PRINT @sub_obj_name
   EXEC SP_GET_DEPENDENCIES @sub_obj_name, @level 
   FETCH NEXT FROM the_cursor_2 INTO @sub_obj_name 
    END
  CLOSE the_cursor_2
  DEALLOCATE the_cursor_2
   END

 IF @level = 3
   BEGIN 
  DECLARE the_cursor_3 CURSOR FOR 
   SELECT DISTINCT REPLICATE('--', @level) + ' ' + c.name FROM dbo.sysdepends a
     INNER JOIN dbo.sysobjects b ON a.id = b.id
     INNER JOIN dbo.sysobjects c ON a.depid = c.id
     WHERE b.name = @obj_name
  OPEN the_cursor_3
  SET @level = @level + 1
  FETCH NEXT FROM the_cursor_3 INTO @sub_obj_name 
  WHILE @@FETCH_STATUS = 0 
    BEGIN 
   PRINT @sub_obj_name
   EXEC SP_GET_DEPENDENCIES @sub_obj_name, @level 
   FETCH NEXT FROM the_cursor_3 INTO @sub_obj_name 
    END
  CLOSE the_cursor_3
  DEALLOCATE the_cursor_3
   END

Answer 1

See this Stackoverflow question for a discussion of sorting querying table foreign key dependencies by depth - which is a similar problem to the one you're discussing. There are at least two working solutions to that problem in the answers and the only real difference to what you're doing is the tables they're crawling. This posting has a DB reverse engineering script that shows how to use a lot of the main data dictionary tables.

Answer 2

for ms sql server you can use CURSOR LOCAL, then the cursor is local to the sproc call and your code becomes much simpler:

CREATE PROCEDURE uspPrintDependencies
(
    @obj_name varchar(300),
    @level int
)
AS
SET NOCOUNT ON
DECLARE @sub_obj_name varchar(300)

if @level > 0 begin
    PRINT Replicate(' ',@level) + @obj_name
end
else begin
    PRINT @obj_name
end

DECLARE myCursor CURSOR LOCAL FOR 
    SELECT 
        DISTINCT c.name 
    FROM dbo.sysdepends a
        INNER JOIN dbo.sysobjects b ON a.id = b.id
        INNER JOIN dbo.sysobjects c ON a.depid = c.id
    WHERE b.name = @obj_name
OPEN myCursor
SET @level = @level + 1
FETCH NEXT FROM myCursor INTO @sub_obj_name 
WHILE @@FETCH_STATUS = 0 BEGIN 
    EXEC uspPrintDependencies @sub_obj_name, @level 
    FETCH NEXT FROM myCursor INTO @sub_obj_name 
END
CLOSE myCursor
DEALLOCATE myCursor
GO