Generating a SP Dependency Tree

  • Hi,

    Does anyone in here know how to generate a stored procedure dependency tree?

     

    For example, SP1 calls SP2 which calls SP3 and SP4.  SP5 (stand alone)

    I need something like

    SP    CalledBy  Level

    SP1     None      0

    SP2      SP1       1

    SP3      SP2        2

    Sp4      SP2        2

    SP5     None        0

    .

    .

    Any one got any ideas?

    Thanks

    Brian

    bferguson@nc.rr.com.nospam

  • There are no 100% guaranteed ways to do this, except the old-fashioned traditional roll-up-your-sleeves-and-get-dirty method.

    Audit the code, document the flow of calls and store the document in a safe place, preferrably in a source version control environment. If you have access to a RDBMS modelling tool, this is also a place where it could be documented, along with the rest of the model.

    /Kenneth

  • Take a look in Enterprise Manager, right-click a procedure and select All Tasks, Display Dependencies. Uncheck the box that says "Show first level dependency only". You should get a list similar to what you are looking for. This list is built using information in the system table dbo.sysdepends.

  • Thanks Chris, that's what I was looking for. I could not remember where this info was stored.

    brian

  • sysdepends is broken, has always been broken and is not 100% accurate.

    If you want to be thorough, and absolutely sure you get everything right, and not missing out on anything, the only way to do this is by manual inspection of the code.

    You cannot trust sysdepends.

    /Kenneth

  • Thanks Kenneth,

    That is part of the reason I was looking for something (sysdepends as Chris suggested, should work great).

    Unfortunately, the code this client has written has less than stellar documentation. I needed a method to find out exactly what is in the databases.

    Thanks

    Brian

  • sysdepends has always been broken (it's part of the Sybase legacy code). However I believe that EM does it differently (like so many other functions) and is correct.

    RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."

  • EM uses the sp_MSdependencies stored proc. I'm testing with that now.

    Thanks everyone

    Brian

  • sp_MSdependencies does uses sysdepends, therefore garbage in ...

     

     


    * Noel

  • Actually there is a piece of code that will show Stored Procedure dependencies and its available at this website.  The name of the SP is funProcWithoutComments and you can find the details for it at the below URL.

    http://www.sqlservercentral.com/columnists/spopovski/viewingthehierarchyofstoredprocedures.asp

    This SP does not use the SYSDEPENDS table.

    Ed

    Kindest Regards,

    Just say No to Facebook!
  • Unfortunately that procedure has this limitations:

    • Cannot handle more than 4000 chars in the sp body
    • if the sp is splitted i more than one row on syscomments it won't work
    • has to be adjusted to include views, tables, and functions -- doable but it needs a good bit of effort

    The best way i believe would be a perl script that used in combination with the actual sysobjects info parse out the code!

    Just my $0.02

     

     


    * Noel

  • When you have 0% to start with and the task is large, an 80 or 90% solution would probably be pretty useful as a start.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Hi

    We can get the dependencies from the execution plan. But this can be achived only with the help of a front end programming language like VB or anything or even simple VB Script that can connect using adodb and then write to a flat file.

    I will write the steps

    1. Open a connect to the required DB

    2. From the connection execute the statement "SET SHOWPLAN_ALL ON"

    3. Now run the required Proc py pasing the required parameters

    4. it will return a recordset/ dataset depending upon the language used.

    5. the value in the Recordset can then be used to find the dependensies with some manipulation.

    You can try the same in query analyzer but the problem is you cant manipulate the result set. for this you may have to copy it to a excel File or set the output of the query analyzer to a file.

    1. Open query analyzer and connect to the database

    2. SET ShowPlan_ALL ON

    3. Run the required SP

    4. You will get a result with the following columns

               StmtText

               StmtId

               NodeId

               Parent

               PhysicalOp

               LogicalOp

               Argument

               DefinedValues

               EstimateRows

               EstimateIO

               EstimateCPU

               AvgRowSize

               TotalSubtreeCost

               OutputList

               Warnings

               Type

               Parallel

               EstimateExecutions

    The columns of intrest are  "Type" , "NodeID", "parentID","StmtText". A few manipulations of these four columns should help you in identifying the dependencies.For SP's the Column "TYPE" will have value "Execute"

    Hope this will be usefull for you.

     


    Regards,

    Jomy John Thomas

    Your Guide to Admin and Design

  • Thanks all for your suggestions, This give me alot more to go on than I originally had.

    brian

  • Not necessarily (imo)

    It might lead you to accept the 80-90% as 'good enough', which it isn't.

    Or worse, it might lead you (or someone else) to think that it's actually 100% accurate. It's better to bite the bullet and do it right the first time.

    Parsing through the code et al might also seem tempting, but then you have to get your hands of some pretty nifty code (which there may be out there, sure) that also can sift through object references in comments etc.

    If you have any dynamic SQL in there anywhere, then parsing won't work either....

    The bottom line is that you can't avoid reviewing the actual code in any case, so why not just do that from the start..?

    just my .02 of course

    /Kenneth

Viewing 15 posts - 1 through 15 (of 15 total)

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