View Object Dependencies in SQL Server 2008

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

USE AdventureWorks
GO
SELECT
OBJECT_NAME (referencing_id) as referencing_entity_name,
obj.type_desc AS referencing_desciption,
referenced_schema_name,
referenced_entity_name,
referenced_server_name,
referenced_database_name
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');
GO

OUTPUT

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

SELECT
OBJECT_NAME (referencing_id) referencing_entity_name,
referenced_schema_name,
referenced_entity_name,
referenced_server_name,
referenced_database_name
FROM sys.sql_expression_dependencies

OUTPUT

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.