SqlDbx Forum

ptoniolo

Member

Object dependencies

In the table properties, when I look for the Dependencies, sometimes I do not find all the uses of the table.

I believe that you use a logic around the old sp_depends to get the list of dependencies, because the list of dependencies in sqldbx mask is the same as what is returned from that procedure.

You should use instead the system view sys.sql_expression_dependencies, e.g.:

select object_schema_name(referencing_id)+'.'+object_name(referencing_id)
from sys.sql_expression_dependencies
where referenced_id = object_id(@object_spec)

This query gives me the complete list of dependencies, even the two (BTW, views) that the sp_depends (and your mask) forgets.

I use sql2008R2.

ptoniolo

Member

Re: Object dependencies

There must be some problem with regression or whatever... I have the same problem again and again: now I cannot find the dependencies of a table in the four views in which it is referenced.
I am using the current version of sqldbx: 4.8 pro 32 bits.

Please check!

sqldbxhelp

Administrator

Re: Object dependencies

We use sp_depends to get list of dependencies.
Results you see should be consistent with sp_depends output.
We do plan to replace sp_depends with  sys.dm_sql_referencing_entities and sys.dm_sql_referenced_entities.

ptoniolo

Member

Re: Object dependencies

We use sp_depends to get list of dependencies.
Results you see should be consistent with sp_depends output.

Yes, I get he same incomplete results from both sp_depends and the sqldbx form.

We do plan to replace sp_depends with  sys.dm_sql_referencing_entities and sys.dm_sql_referenced_entities.

I believe you should do it ASAP, because the results I get from sp_depends are often inconsistent!

Thanks
Pietro