Query Analyzer and sysdepends

Question

Query Analyzer and sysdepends

singhster

Ten Centuries

Points: 1387
More actions
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?

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

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

K. Brian Kelley SSC Guru Points: 114642 More actions · Answer 1

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

singhster Ten Centuries Points: 1387 More actions · Answer 2

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

K. Brian Kelley SSC Guru Points: 114642 More actions · Answer 3

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

singhster Ten Centuries Points: 1387 More actions · Answer 4

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

K. Brian Kelley SSC Guru Points: 114642 More actions · Answer 5

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

Veronica Lee SSC Rookie Points: 39 More actions · Answer 6

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

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 7

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.