Post reply

Query Analyzer and sysdepends

  • August 12, 2004 at 4:46 am

    #109374

    I've got a stored procedure which is called by about 20 other stored procedures.  However when I look at the dependencies in Query Analyzer it shows that there are no SPs calling it.

    However, if I recompile an SP that calls it, the SP appears in the dependencies list.  Any ideas?

  • August 12, 2004 at 8:20 am

    #518682

    Yes. When the stored procedure was originally created, it referenced the other 20 or so stored procedures but those stored procedures hadn't been created yet. This doesn't stop the stored procedure creation process. As a result, nothing went into sysdepends. When you recreated the stored procedure, the other procedures existed and SQL Server was able to write the entries in sysdepends.

    K. Brian Kelley
    @kbriankelley

  • August 12, 2004 at 8:28 am

    #518686

    Is there any way or refreshing the sysdepends table, short of recompiling every stored procedure?

    Does anyone use the dependencies to determine where a stored procedure is called from, or is there a more simple method?

    Thanks

     

     

  • August 12, 2004 at 8:49 am

    #518692

    I don't use sysdepends. Sysdepends is notoriously inaccurate. Unfortunately, shorting of parsing each stored procedure's code I don't know of a viable alternative.

    K. Brian Kelley
    @kbriankelley

  • August 12, 2004 at 8:54 am

    #518696

    Thanks Brian, was looking for a cheeky way out, but it's not to be..

  • August 12, 2004 at 8:57 am

    #518699

    There are some third party products that do this sort of thing (the names escape me... only 2 hours sleep last night). But I don't know if they parse or if they use sysdepends. Should be fairly simple using some code with regular expressions (this rules out T-SQL) by first querying sysobjects for the owners and names of the stored procedures and then parsing through syscomments.

    K. Brian Kelley
    @kbriankelley

  • February 21, 2005 at 12:16 pm

    #543356

    HELLO BRIAN,

    Not sure if this is the best way to add on a question to this topic, but could not find alternative...  I just loaded a script with about 20 stored procedures.  they were in alpha order rather than ordered by called procedures prior to the calling procedures.  should I worry that I got this error or can I just let it go since 'sysdepends is notoriously inaccurate'.  does it need to be fixed?  should i recompile or something?  what is the worst that could happen?

    Thank you,

    vlee

  • February 21, 2005 at 12:43 pm

    #543359

    well if the procs work.. nothing. SysDepends is used when ou ask sql server to show the object dependencies (spa needs table1,view2 and vice-verca)... but this is not always accurate. So I guess the worst that could happen is that you delete a proc that is actually needed but that sql server "said" it wasn't.

Viewing 8 posts - 1 through 7 (of 7 total)

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