Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Parse the dependency chain of an object Expand / Collapse
Author
Message
Posted Thursday, January 17, 2008 12:20 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, September 22, 2014 11:18 AM
Points: 305, Visits: 567
Comments posted to this topic are about the item Parse the dependency chain of an object

Jesse McLain
jesse@jessemclain.com
www.jessemclain.com
www.jessesql.blogspot.com
Post #444162
Posted Tuesday, February 26, 2008 3:11 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, May 17, 2013 7:45 AM
Points: 5, Visits: 10
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.
Post #460118
Posted Wednesday, February 27, 2008 9:56 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, September 22, 2014 11:18 AM
Points: 305, Visits: 567
BryanR (2/26/2008)
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.


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


Jesse McLain
jesse@jessemclain.com
www.jessemclain.com
www.jessesql.blogspot.com
Post #461036
Posted Wednesday, September 17, 2008 9:36 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, December 24, 2013 4:42 AM
Points: 460, Visits: 2,523
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


.
Post #571448
Posted Friday, July 31, 2009 2:12 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, January 26, 2011 1:32 PM
Points: 146, Visits: 327
Jacob,

I cannot find that link - did it move?

Best,
Doug
Post #763378
Posted Friday, July 31, 2009 10:06 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, December 24, 2013 4:42 AM
Points: 460, Visits: 2,523
Douglas,
Here is the correct link http://blog.beyondrelational.com/2008/09/find-dependent-objects-recursively.html

Thanks
Jacob


.
Post #763518
Posted Monday, February 15, 2010 2:27 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Friday, October 31, 2014 1:35 AM
Points: 1,545, Visits: 762
If i more than 4 levels of nesting in the objects with dependencies the proc doesn work... any other alternatives?

For better assistance in answering your questions
Click Here
Post #865419
Posted Tuesday, April 24, 2012 7:34 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Friday, October 17, 2014 5:02 PM
Points: 485, Visits: 1,368
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:

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 = 0
FROM sys.sysobjects S
WHERE S.Name = 'ENTER_YOUR_OBJECT_NAME_HERE'

DECLARE @Name varchar(1000)
DECLARE @DependsOn varchar(1000)
DECLARE @DepLevel smallint
DECLARE @ObjNum int
SET @ObjNum = 1
WHILE 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 + 1
END

SELECT * FROM @DepTree

Post #1289499
Posted Saturday, August 25, 2012 1:13 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, September 10, 2012 9:37 PM
Points: 1, Visits: 3
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
Post #1350066
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse