I'd be interested if anyone could come up with a way to make SYSDEPENDS reliable.
Try the following:
create table a (id int)
go
create procedure b as begin select id from a end
go
SELECT O.id, O.name as P1, O2.name as P2, D.depid, D.depnumber
FROM sysdepends D
INNER JOIN sysobjects O ON O.id = D.id
INNER JOIN sysobjects O2 ON O2.id = D.depid
where o.name='b'
go
exec b
go
drop table a
go
create table a (id int)
go
SELECT O.id, O.name as P1, O2.name as p2, D.depid, D.depnumber
FROM sysdepends D
INNER JOIN sysobjects O ON O.id = D.id
INNER JOIN sysobjects O2 ON O2.id = D.depid
where o.name='b'
go
exec b
go
SELECT O.id, O.name as P1, O2.name as p2, D.depid, D.depnumber
FROM sysdepends D
INNER JOIN sysobjects O ON O.id = D.id
INNER JOIN sysobjects O2 ON O2.id = D.depid
where o.name='b'
go
drop table a
drop procedure b
go
The message output will be:
(1 row(s) affected)
(0 row(s) affected)
(0 row(s) affected)
(0 row(s) affected)
(0 row(s) affected)
The output indicates that SYSDEPENDS has no information on what B depends on, although it still executes OK and hence clearly depends on the recreated table A!
Derek