SQLServerCentral / Article Discussions / Article Discussions by Author / Discuss content posted by Jesse McLain / Parse the dependency chain of an object / Latest Posts

RE: Parse the dependency chain of an object

milan.technical — Sat, 25 Aug 2012 13:13:30 GMT

Hey Jacob, I just happen to look into your code. It seems like, as you are using sys.sysdepends, it may not work if the referenced object is created after referencing object. Can you please provide some other solution?Thanks'Milan

RE: Parse the dependency chain of an object

SQL_ME_RICH — Tue, 24 Apr 2012 19:34:56 GMT

I know this script and thread are a few years old, but this is a very helpful dependency finder for PROCS, so I wanted to share a slight change that one of my developers made to the original script. He is not a fan of temp tables, so he used a table variable instead. Here's the T-SQL:[code="sql"]DECLARE @DepTree TABLE (ObjNum int identity(1,1) not null, Name varchar(1000), DependsOn varchar(1000), ObjectType char(2), DepLevel smallint)INSERT INTO @DepTree (Name, DependsOn, ObjectType, DepLevel)SELECT DependsOn = S.Name, S.Name, ObjectType = S.XType, DepLevel = 0FROM sys.sysobjects SWHERE S.Name = 'ENTER_YOUR_OBJECT_NAME_HERE'DECLARE @Name varchar(1000)DECLARE @DependsOn varchar(1000)DECLARE @DepLevel smallintDECLARE @ObjNum intSET @ObjNum = 1WHILE EXISTS(SELECT 1 FROM @DepTree WHERE ObjNum = @ObjNum)BEGIN SELECT @Name = Name, @DependsOn = DependsOn, @DepLevel = DepLevel FROM @DepTree WHERE ObjNum = @ObjNum -- this block finds objects that the current object of interest depends on (moving _down_ the dependency chain): IF @DepLevel >= 0 INSERT INTO @DepTree (Name, DependsOn, ObjectType, DepLevel) SELECT DISTINCT S1.Name, DependsOn = S2.Name, ObjectType = S2.XType, DepLevel = @DepLevel + 1 FROM sys.sysdepends DP JOIN sys.sysobjects S1 ON S1.ID = DP.ID JOIN sys.sysobjects S2 ON S2.ID = DP.DepID WHERE S1.Name = @DependsOn ORDER BY 1, 3, 2 -- this block finds objects that depend on the current object of interest (moving _up_ the dependency chain): IF @DepLevel <= 0 INSERT INTO @DepTree (Name, DependsOn, ObjectType, DepLevel) SELECT DISTINCT S2.Name, DependsOn = S1.Name, ObjectType = S2.XType, DepLevel = @DepLevel - 1 FROM sys.sysdepends DP JOIN sys.sysobjects S1 ON S1.ID = DP.DepID JOIN sys.sysobjects S2 ON S2.ID = DP.ID WHERE S1.Name = @Name ORDER BY 1, 3, 2 SET @ObjNum = @ObjNum + 1ENDSELECT * FROM @DepTree[/code]

RE: Parse the dependency chain of an object

Sriram.RM — Mon, 15 Feb 2010 02:27:58 GMT

If i more than 4 levels of nesting in the objects with dependencies the proc doesn work... any other alternatives?

RE: Parse the dependency chain of an object

jacob sebastian — Fri, 31 Jul 2009 22:06:52 GMT

Douglas,Here is the correct link [url]http://blog.beyondrelational.com/2008/09/find-dependent-objects-recursively.html[/url]ThanksJacob

RE: Parse the dependency chain of an object

Douglas Osborne-456728 — Fri, 31 Jul 2009 14:12:01 GMT

Jacob,I cannot find that link - did it move? Best,Doug

RE: Parse the dependency chain of an object

jacob sebastian — Wed, 17 Sep 2008 21:36:07 GMT

Good post!Another option is to use a recursive CTE that returns the entire dependency chain in a single query. I wrote such a function and the code is posted here: http://www.sqlserverandxml.com/2008/09/find-dependent-objects-recursively.html

RE: Parse the dependency chain of an object

Jesse McLain — Wed, 27 Feb 2008 09:56:04 GMT

[quote][b]BryanR (2/26/2008)[/b][hr]Useful, thanks!One minor point: you've declared ObjectType as char(1), but it should be char(2) to match the xtype field in sysobjects. Functions have xtype='FN', so cause the batch to fail.Bryan.[/quote]Great catch, thanks for the post! I went ahead and updated the code. When I wrote this, I was reverse-engineering a database with over 400 stored procedures, some of which nested 20 layers deep. I could not have done it and stayed sane without the overview that these sorts of queries provide. The unfortunate thing is the breaks in the dependency chains that SQL Server 2005 allows. I had to augment this process by querying syscomments for names of nested procedures.Thanks,Jesse

RE: Parse the dependency chain of an object

BryanR — Tue, 26 Feb 2008 03:11:33 GMT

Useful, thanks!One minor point: you've declared ObjectType as char(1), but it should be char(2) to match the xtype field in sysobjects. Functions have xtype='FN', so cause the batch to fail.Bryan.

Parse the dependency chain of an object

Jesse McLain — Thu, 17 Jan 2008 00:20:18 GMT

Comments posted to this topic are about the item [B]Parse the dependency chain of an object[/B]