Viewing object dependencies within a database as well as between databases and servers has become easier in SQL Server 2008. SQL Server 2008 introduces a catalog view (sys.sql_expression_dependencies) and dynamic management functions (sys.dm_sql_referenced_entities & sys.dm_sql_referencing_entities) that can help in dependency tracking.
In this article, we will see how to use the catalog view sys.sql_expression_dependencies to do object dependency tracking. In a forthcoming article, we will see how to use the dynamic management functions
View Object Dependencies within a Database
Let us assume we want to see the tables and columns referenced in the view Sales.vSalesPerson
Here’s the same query to try out
OBJECT_NAME (referencing_id) as referencing_entity_name,
obj.type_desc AS referencing_desciption,
FROM sys.sql_expression_dependencies AS sqled
INNER JOIN sys.objects AS obj ON sqled.referencing_id = obj.object_id
WHERE referencing_id = OBJECT_ID(N'Sales.vSalesPerson');
Shown above are the table and columns that the view vSalesPerson is dependent upon.
View Object Dependencies between Databases (Cross-database)
Also known as Cross-database dependencies, let us see an example to view dependencies when one database references objects from a different database
For this example, create a sample database testdb that references a table from the AdventureWorks database, as shown below:
Now in order to view cross-database dependencies between the procedures and the tables, use the catalog view sys.sql_expression_dependencies as shown below
OBJECT_NAME (referencing_id) referencing_entity_name,
Note 1: sys.sql_expression_dependencies can also be used to track dependencies for Filtered Index Expressions too.
Note 2: In SQL Server 2000, we have the sysdepends table.