T-sql Puzzler - Crawling Object Dependencies

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

Solution 1:

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

CREATEPROCEDURE uspPrintDependencies
(
    @obj_name varchar(300),
    @levelint
)
ASSET NOCOUNT ONDECLARE@sub_obj_name varchar(300)

if @level>0begin
    PRINT Replicate(' ',@level) +@obj_name
endelsebegin
    PRINT @obj_name
endDECLARE myCursor CURSORLOCALFORSELECTDISTINCT c.name 
    FROM dbo.sysdepends a
        INNERJOIN dbo.sysobjects b ON a.id = b.id
        INNERJOIN dbo.sysobjects c ON a.depid = c.id
    WHERE b.name =@obj_name
OPEN myCursor
SET@level=@level+1FETCH NEXT FROM myCursor INTO@sub_obj_name 
WHILE @@FETCH_STATUS =0BEGINEXEC uspPrintDependencies @sub_obj_name, @levelFETCH NEXT FROM myCursor INTO@sub_obj_name 
ENDCLOSE myCursor
DEALLOCATE myCursor
GO

Solution 2:

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.