Stored Procedure Call Hierarchy

Question

Stored Procedure Call Hierarchy

cneuhold

Hall of Fame

Points: 3375
More actions
October 13, 2007 at 10:30 pm

#182832

Comments posted to this topic are about the item Stored Procedure Call Hierarchy

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

rbalaerd SSC Rookie Points: 49 More actions · Answer 1

Hi,

I need to extract the dependent Stored procedure names across databases also. The current code displays only the dependent stored procedures that are present in the parent stored procedure database.

For Eg:

If Parent stored procedure named MainSP is present in Database1 and

its child stored procedures,

a. ChildSP1 is present in Database1

b. ChildSP2 is present in Database2

c. ChildSP3 is present in Database1

Then the current code will display only ChildSP1 and ChildSP3 in the tree and not ChildSP2. I need ChildSP2 also to be listed in the tree

If any one can has solution for it mail to rbalaerd@yahoo.co.in

- Bala

n2463230 SSC Journeyman Points: 76 More actions · Answer 2

Hi,

Thank you for the script for viewing the hierarchy. But I am still suspicious about the outcome that I am getting after executing the Script. Can you please explain the working of the script? Because there are lots of places where the same stored procedure is called, but its hierarchy is not being displayed. So either the script is having something missing or the script is not 100% correct.

If you can explain me the script, we both can work on it together and make the script work as expected.

Thank you.

YSLGuru SSC-Insane Points: 21661 More actions · Answer 3

This did not work for me. I have a SP called LF5_JOb that contains the line EXECUTE LF5_Job2 and your code did not show this

Kindest Regards,

Just say No to Facebook!

YSLGuru SSC-Insane Points: 21661 More actions · Answer 4

All - I ended up figuring out a way to do this; SSMS's View Dependencies. If you select the SP you want to get the hierarchy for and select Objects on which SP_Name depends you will get exactly that.

NOTE: If the SP you select has a lengthy hierarchy it can take several minutes for view decencies to finish so don't assume that SSMS has crashed or locked up.

Kindest Regards,

Just say No to Facebook!

YSLGuru SSC-Insane Points: 21661 More actions · Answer 5

UPDATE: After some addiotnal checking I found that there are 2 built in DMM's that can be used to shows teh same info that you see in SSMS's View Dependencies; sys.dm_sql_referencing_entities & sys.dm_sql_referenced_entities

The below shows everything that calls the listed SP:

SELECT referencing_schema_name, referencing_entity_name
FROM sys.dm_sql_referencing_entities('dbo.LF5_main', 'Object')

The below shows everything that the SP calls:

SELECT referenced_entity_name
FROM sys.dm_sql_referenced_entities('dbo.LF5_main', 'Object')

So what's left is figuring out a way to use these recursively to get the complete hierarchy.

Kindest Regards,

Just say No to Facebook!