Select the product you need help with

BUG: Reference to Deferred Object in Stored Procedure Will Not Show in Sp_depends

Article ID: 201846 - View products that this article applies to.

This article was previously published under Q201846

SYMPTOMS

The output of sp_depends for an object created with dependencies on objects whose name resolution is deferred will not be correct. For an example of this, see the MORE INFORMATION section of this article.

Back to the top | Give Feedback

STATUS

Microsoft has confirmed that this is a problem in the Microsoft products that are listed at the beginning of this article.

Back to the top | Give Feedback

MORE INFORMATION

To reproduce this problem, use the following Transact-SQL code:

use pubs
go
drop proc abc1
go
create proc abc1
as
select au_id into #a1 from authors
insert into #a1 select au_id from titleauthor
go
sp_depends abc1
go

SQL Server 6.5 will return the following:

In the current database the specified object references the following:

name                                     type             updated selected
---------------------------------------- ---------------- ------- --------
dbo.authors                              user table       no      no
dbo.titleauthor                          user table       no      no

(1 row(s) affected)

SQL Server 7.0 will return the following:

In the current database, the specified object references the following:

name                 type               updated        selected         column name
-------------------- ------------------ -------------- ---------------- -----------
dbo.authors          user table         no             no               au_id

Back to the top | Give Feedback