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.

  • How to get Windows Log-in User Name for a SQL Log in User
  • Display top n records and consolidate the rest of the rows
  • Entity Framework cached query plan performance degrades with different parameters
  • How to assign default value for DBNull values from DataTable when performing bulk insert/update as IEnumerable?
  • Unexpiring an expired SQL Server Login password (Password cannot be changed)
  • Why use a READ UNCOMMITTED isolation level?
  • 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.

    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.

    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.