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.

About The Author

Suprotim Agarwal, MCSD, MCAD, MCDBA, MCSE, is the founder of DotNetCurry, DNC Magazine for Developers, SQLServerCurry and DevCurry. He has also authored a couple of books 51 Recipes using jQuery with ASP.NET Controls and a new one recently at The Absolutely Awesome jQuery CookBook.

Suprotim has received the prestigous Microsoft MVP award for nine times in a row now. In a professional capacity, he is the CEO of A2Z Knowledge Visuals Pvt Ltd, a digital group that represents premium web sites and digital publications comprising of Professional web, windows, mobile and cloud developers, technical managers, and architects.

Get in touch with him on Twitter @suprotimagarwal, LinkedIn or befriend him on Facebook