sp_depends not showing dependencies

[dBjason]

SQL Server 8

Hello,

I'm trying to run sp_depends on a view. Yesterday, it gave me both sides of dependencies (what it depends on, and what processes depends on it).

This morning, however, I'm just getting what the view references and/or depends on. Not good because I want to change a couple of JOINs in the view, but before I do I want to get record counts from whatever sp's/views/funcs depend on it. But now it won't tell me what's referencing my view.

Could there be some setting that's causing this malfunction?

Any ideas would be greatly appreciated.

Thanks,
Jason

 

[vongrunt]

AFAIK it is known that sp_depends is not the most reliable thing in the universe but... is any object up/down dependency chain modified in the meantime?

------
[small]select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')[/small]

 

[dBjason]

No, nothing at this point. Had to get 3rd party software to search through our databases to find out where it's referenced.

I figured it wouldn't be able to see dependencies that were dynamic (ie: queries built in a string in an sp), but wow... That was a first!

 

[ClayG]

I think its a problem of timing of the creation of objects. If A refers to B, if you define A first (which is usually allowed, eg procedure A refers to table B), then db cannot record dependency because B does not exist yet (especially because dependencies are by object IDs, not name, no object ID yet).

For SP->Table (not perfect):
Select PAR.Name, CHLD.Name
from Sysobjects PAR, SysComments SC, Sysobjects CHLD
where SC Like '%'+ SO.name +'%'
and CHLD.ID = SC.ID