Stored Procedure Call Hierarchy

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

  • 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

    - Bala

  • 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.

  • 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!
  • 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!
  • 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!

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

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