Sql Server: all object dependencies
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.
3 Solutions collect form web for “Sql Server: all object dependencies”
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
For actual objects (what objects does table ‘foo’ depend on):
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.
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.
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.