Sysdepends Is Annoying

I know I shouldn't rely on sysdepends because it's got all sorts of problems with broken dependency chains, etc. But it's better than nothing for finding dependencies, short of rolling your own t-sql parser. So I use sysdepends anyway, with all its faults. I just don't rely on it. anyway, to the point, just an fyi really:

Here's something about sysdepends discovered today that annoys me. If you introduce a dependency of a proc on a table by selecting from the table in the proc, the sql parser discovers this and dutifully adds a row to sysdepends. Very well. But if you do the same select into a temp table, sysdepends doesn't pick up the dependency! Here's what I mean:


use master
go

drop database test_sysdepends
go

create database test_sysdepends
go

use test_sysdepends
go

create table MyTable01 (id int)
go

create proc MyProc01
as
select id from MyTable01
go

create proc MyProc02
as
create table #t (id int)

-- comment this next line out and the dependency is picked up.
-- but as long as we insert into the temp table, we don't pick it up... :(
insert into #t
select id from MyTable01

select id from #t
go


select
so2.name as parent
,so1.name as dependent
from sysdepends d
join sysobjects so1 on d.id=so1.id
join sysobjects so2 on d.depid=so2.id
go


results:


parent dependent
--------- ---------
MyTable01 MyProc01


i am seeing this on 2005 sp1, also on 2000 (msde)

grrr...

www.elsasoft.org