Is there a way to identify which objects are dependent on other objects within a SQL Server database? I need to modify the table structure to add new columns. However, before making any changes I want to make sure that I understand all the object dependencies. Is there a way in SQL Server 2008 to quickly identify all the object dependencies?
In the earlier versions of SQL Server object dependencies were tracked using the ID of the object, as a result it was tough to keep track of Object Dependencies. However, in SQL Server 2008 Objects are tracked using the name of the object rather than object IDs. The biggest advantage of this approach is that you can track object dependency even after the object is removed from the database and you can also track even if an object is yet to be created in the database.
In SQL Server 2008 there are two new Dynamic Management Functions and a System View introduced to keep track of Object Dependencies. The newly introduced Dynamic Management Functions in SQL Server 2008 to keep track of object dependencies are sys.dm_sql_referenced_entities and sys.dm_sql_referencing_entities. The newly introduced system view to keep track of object dependency is sys.sql_expression_dependencies.
SQL Server 2008 database engine automatically keeps track of object dependency information whenever referencing entities are created, dropped or altered and it records this information in SQL Server system catalogs. A dependency is created between two objects when one object appears by name inside a SQL statement stored in another object. The object which is appearing inside the SQL expression is known as referenced entity and the object which has the SQL expression is known as a referencing entity.
The sys.sql_expression_dependencies system view holds one record each for a user defined object which has dependency on other object within the current database. A dependency between two objects is created when one object calling the referenced object appears by name in a SQL expression of another object. There are basically two types of dependencies tracked by the database engine in SQL Server 2008 namely Schema-bound Dependency and Non-schema-bound Dependency.
Schema-bound dependency: - A schema-bound dependency is a relationship that exists between two objects that prevents referenced objects from being dropped or modified as long as the referencing object exists. A quick example of schema-bound dependency will be a view or a user defined function which is created using WITH SCHEMABINDING clause.
Non-schema-bound dependency: - A non-schema-bound dependency is a relationship which exists between two objects which doesn't prevent the referenced object from being dropped or modified.
The sys.dm_sql_referenced_entities Dynamic Management Function returns one row for each user defined object which is referenced by name within the definition of a specified referencing object. For example, if a user defined view is the specified referencing object, then by using sys.dm_sql_referenced_entities dynamic management function you can return all the user defined objects that are referred in the view definition such as tables, functions etc.
The sys.dm_sql_referencing_entities Dynamic Management Function returns one record for each user defined object within the current database which refers to another user defined object by name. For example, if there is a view which refers to three tables then this function will return three records one for each table reference. This function will also return dependency information for Schema-bound or Non-schema-bound entries, Database level and Server level DDL triggers.
Let us go through an example to understand how the SQL Server 2008 database engine tracks object dependencies.
Use the below T-SQL script to create a table named Employee.
Execute the below T-SQL script to create usp_GetEmployeeName stored procedure. This procedure refers to Employee table which was created using the above script.
Now that we have a sample table and stored procedure setup, the following queries can be used to find the referenced and referencing information.
Execute the below scripts which queries "sys.dm_sql_referencing_entities" dynamic management function to find out all objects which are referencing to "Employee" table.
Execute the below scripts which queries "sys.dm_sql_referenced_entities" dynamic management function to find out all objects which are referenced to "usp_GetEmployeeName" stored procedure.
Execute the below scripts which queries "sys.sql_expression_dependencies" system view to find out all the objects which are referenced by "usp_GetEmployeeName" stored procedure.
Here is an example output of these queries for my test.
Does this mean that any reference to entities in dynamic SQL will be picked up?
Will table foobar be picked up as being referenced? If so, this would be very handy even though dynamic SQL should not be standard practice. Sometimes, you get that with inherited systems.
Could you suggest any way to get list of objects that are being accessed in scheduled jobs?