Sql Server: all object dependencies

Question

Hi,

Is there any place where I can find all possible object type dependencies in Sql Server?

What I mean by "object dependency" is a list of object that one object can depend on. For example, TABLE can depend on SCHEMA, TYPE, TABLE, FUNCTION, etc.

Answer 1

For actual objects (what objects does table 'foo' depend on):

sys.sql_dependencies

Is mostly accurate. There is also the SMO helper, DependencyWalker.

As a general type question (ie. what type of objects can a table depend on), you just need to go through the spec on MSDN for each object CREATE/ALTER statement and read carefully everything.

Answer 2

Aside from dynamic SQL, technically, SQL Server does keep track of dependencies. However, until SQL Server 2008, its tracking was not reliable because it only updated dependencies if all dependent entities existed at time of creation. SQL Server 2008 significantly improved dependency tracking.

In SQL Server 2000 and 2005, you can query against sys.sql_dependencies to get a list of dependencies.

In SQL Server 2008, you should use sys.sql_expression_dependencies See sys.sql_expression_dependencies for more.

EDIT I think I may have misinterpreted your question. It sounds like you are looking for a list of object types on which a TABLE type object can depend. Directly or indirectly, it would be any object type in the system. If we only want "direct" dependencies, then it depends on what is meant by "direct". For example, does a trigger that references a view count as a direct dependency of the trigger table to the view?

EDIT As far as I know, there is no enumerated list of all possible dependencies between types. The best that you could achieve is to analyze the types that do depend on other types in a given database using something like:

Select DependentObj.Type, ReferencedObj.Type
from sys.sql_dependencies As D
    Join sys.sysobjects As ReferencedObj
        On ReferencedObj.id = D.referenced_major_id 
    Join sys.sysobjects As DependentObj
        On DependentObj.id = D.object_id 
Group By DependentObj.Type, ReferencedObj.Type

Answer 3

A persisted computed column in a table could depend on a user defined function.

A non-deterministic user-defined function can depend on a table.

A constraint could cause a table to depend on a table.

ad nauseum.

You could pick any pair of object types and we might be able to come up with a dependency.

There are obviously some restrictions in the various SQL Server features, but I'm not aware of any comprehensive matrix of all possible allowed and disallowed dependencies.