Use of "sysdepends"

Question

Use of "sysdepends"

Linson.Daniel

Ten Centuries

Points: 1153
More actions
June 16, 2009 at 7:26 am

#75483

From my understanding of the sysdepends catalog view, i understand it would hold the dependency information between objects (views, procedures, and triggers), and the objects (tables, views, and procedures)
But then I checked the dependency info of a view...its definition would be as such (sample)
CREATE VIEW ViewA
AS
SELECT * FROM
dbA.dbo.TableA
and then I queried the sysdepends for the dependencies for the View "ViewA" using the following query
select object_name(id),
object_name(depid)
from sysdepends
where id = object_id(N'Employees')
But to my surprise , no records were returned.
Now ofcourse we can see from the definition of the ViewA that it depended on the table "TableA" from a database "dbA".
Then why isn't this information stored here in the sysdepends view? :unsure:
But for the other views that does not refer tables from other databases(like the example shown above) , the dependency info are accurate.
Could someone explain why is it so?

Viewing 3 posts - 1 through 2 (of 2 total)

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

Adi Cohn-120898 SSC-Dedicated Points: 33944 More actions · Answer 1

Don't trust the information from sysdepends table. There are few cases that you will not see the dependency of objects. Beside the scenario that you showed there are other scenarios that will cause the information to be not true. Here is one example:

create proc MyDemoProc as

select * from NoneExistingTable

go

--Will not show you the dependency

select * from sysdepends where id = object_id('MyDemoView')

go

create table NoneExistingTable (i int)

go

--After creating the table, it will not show the dependency

select * from sysdepends where id = object_id('MyDemoView')

go

drop table NoneExistingTable

drop proc MyDemoProc

Adi

--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

John H Marsh SSChampion Points: 11671 More actions · Answer 2

Hello,

May be take a look at this article. In particular the paragraph “Cross-Database and Cross-Server Dependencies”:-

http://sqlblog.com/blogs/aaron_bertrand/archive/2008/09/09/keeping-sysdepends-up-to-date-in-sql-server-2008.aspx

Regards,

John Marsh

www.sql.lu
SQL Server Luxembourg User Group