TSQL for dependency hierarchy

Stubey

Hi

i have a stored proc that searches through stored proc etc text and returns the name if some text is found in it.

I would like to extend this so whne the stored procedure name is retrieved it checks to see if that stored proc is used in another stored proc. Thus building up a tree of dependencies (i dont want to use the sysdepend function). im sure there must be TSQL around that does this or could someone show me how to do this. My stored porc sample is below

TIA

Stu

DECLARE @cSearch varchar(100)

SET @cSearch='%'+LTRIM(RTRIM(@p_cCallName))+'%'

-- sql Server 2005

IF CHARINDEX('9.00',@@VERSION)<>0

SELECT DISTINCT so.name

FROM sys.objects so

INNER JOIN sys.Sql_modules sc

ON sc.object_id=so.object_id

WHERE so.type IN('P','TR','FN')

AND sc.definition LIKE @cSearch

AND so.name<>@p_cCallName

ORDER BY name

rusag2

You can use CTE (lots of documentation on line). It'll be tricky to avoid an infinate loop. But it's doable.

Stubey

Hi,

Thanks for your reply CTE are good if you have hierarchical data

In my example what would you say the parent child relationship is

Ive tried object_id as below. and it doesnt work. Do you have any ideas please

WITH CTEResultDepends (objectId,spName,levelid)

AS

(

SELECT objectId=so.object_id,

spName=so.name,

levelId=0

FROM sys.objects so

INNER JOIN sys.Sql_modules sc

ON sc.object_id=so.object_id

WHERE so.type IN('P','TR','FN')

AND sc.definition LIKE @cSearch

AND so.name<>@p_cCallName

UNION ALL

SELECT objectId=so.object_id,

spName=so.name,

levelId+1

FROM sys.objects so

INNER JOIN sys.Sql_modules sc

ON sc.object_id=so.object_id

INNER JOIN CTEResultDepends rd

ON sc.object_id=rd.objectid

WHERE so.type IN('P','TR','FN')

AND sc.definition LIKE '%'+so.name+'%'

)

SELECT spName,

levelid

FROM CTEResultDepends

NorthwindDBA

Use cursors to loop thru the procedures per level of dependency.

something like this...

DECLARE @cSearch varchar(100)

SET @cSearch='%'+LTRIM(RTRIM(@p_cCallName))+'%'

-- sql Server 2005

IF CHARINDEX('9.00',@@VERSION)<>0

declare c1cursor for
SELECT DISTINCT so.name

FROM sys.objects so

INNER JOIN sys.Sql_modules sc

ON sc.object_id=so.object_id

WHERE so.type IN('P','TR','FN')

AND sc.definition LIKE @cSearch

AND so.name<>@p_cCallName

ORDER BY name

open c1
fetch next c1
into @p_cCallName

-- place loop 1 here (1st level)

SET @cSearch='%'+LTRIM(RTRIM(@p_cCallName))+'%'

-- sql Server 2005

IF CHARINDEX('9.00',@@VERSION)<>0

declare c2cursor for
SELECT DISTINCT so.name

FROM sys.objects so

INNER JOIN sys.Sql_modules sc

ON sc.object_id=so.object_id

WHERE so.type IN('P','TR','FN')

AND sc.definition LIKE @cSearch

AND so.name<>@p_cCallName

ORDER BY name

open c2
fetch next c2
into @p_cCallName

-- place loop 2 here (2nd level)

-- REPEAT FOR UP TO THE NUMBER OF LEVELS OF DEPENDENCIES DESIRED

-- end loop 2

-- end loop 1

---------------------------------------

you may also want to track the depth of the dependency.. or better yet, the hierarchy.

Stubey

Hi,

Thanks for your repsonse and im sure the cursor idea would work but maybe somewhat memory intensive as the levels increase. However the code could get a bit undweadling if you had say 20 levels of depdencies.Im looking for a more neater solution if possible

Thanks

rusag2

Depending on which you call parent and which you call child....

It's a bit misleading...one proc might call two or more other procs....therefore the calling proc would be a "Parent" and the called procs would be "children". But that may be a bit misleading....one proc may have dozens "parent procs" and there is no real "generational" relationship (grandchild procs) etc.

where Parent.ProcDefinition like '%' + Child.ProcName + '%'

NorthwindDBA

hello,

in creating nested procedures, keep the depth to a minimum. the ideal would be a maximum of 4 levels. a 5th or 6th level would be too difficult to maintain. if you have hierachy of 20 levels, i would say you need to restructure your design.

in terms or the memory usage intensity, how many procedures do you have in your database how many of them are dependent on your particular procedure I'm sure this will have an inverse exponential decline as you go to deeper levels. assumming the procedure has 20 dependencies on the first level, average of 10 each on the second, average of 5 each on the third, 2 on the fourth, and may be 1 on the fifth, i do not think this would take long.

if you're looking for a different approch, you could use a subqueries. but then again, you'll be using a subquery within a subquery, within a subquery mimmicking a cursor loop.

hope this helps..