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

Are you using SQL 2005? If so, you can probably do this in a single select statement using a CTE. If not, you can probably still do it in a single non-recursive SP using a temporary table. Just keep grabbing any rows with children left. If I'm over my cold by tomorrow I'll try to write something. — Tom H, Dec 19 '08 at 00:47
Cool, I would like to see this attempted as a CTE. Is it just me... but CTEs make my brain hurt a little more than usual. — BuddyJoe, Dec 19 '08 at 01:05
Just another thought. Collaborating on pieces of code on Stackoverflow is soooo cool! I wish this website was around 10 years ago when I was first starting out. — BuddyJoe, Dec 19 '08 at 01:09
Heh... it looks like NCX's link actually has both of the answers that I was thinking of — Tom H, Dec 19 '08 at 01:47

score 6 · Accepted Answer · answered Dec 19 '08 at 00:42

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

Sweet! This is what I had before the cursor collision stuff (well, minus the local keyword). didn't know there was a local keyword. I will incorporate this into my code tomorrow and try it out. — BuddyJoe, Dec 19 '08 at 01:02
@[Bruno Tyndall]: there's another keyword forward_only or fast_foward or something like that that you should probably use also as an optimization — Steven A. Lowe, Dec 19 '08 at 03:39

score 3 · Answer 2 · edited May 23 '17 at 12:13

3

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.

edited May 23 '17 at 12:13

Community

1
1

answered Dec 19 '08 at 00:01

ConcernedOfTunbridgeWells

64,444
15
143
197

Thanks, this will help me on a few T-SQL problems I'm working on. – BuddyJoe Dec 19 '08 at 01:04

T-SQL Puzzler - Crawling Object Dependencies

2 Answers2

Linked