February 10, 2011

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

sql_expression_dependencies

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

sql_expression_dependencies

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:

cross-database dependency

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

cross-database dependency

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.


Did you like this post?
kick it on DotNetKicks.com
subscribe via rss subscribe via e-mail
print this post follow me on twitter


About The Author

Suprotim Agarwal, ASP.NET Architecture MVP works as an Architect Consultant and provides consultancy on how to design and develop Web applications.

Suprotim is also the founder and primary contributor to DevCurry, DotNetCurry and SQLServerCurry. He has also written an EBook 51 Recipes using jQuery with ASP.NET Controls.

Follow him on twitter @suprotimagarwal

 
  Feedback:

comments

0 Responses to "View Object Dependencies in SQL Server 2008"
 

Copyright © 2009-2014 All Rights Reserved for SQLServerCurry.com by Suprotim Agarwal | Terms and Conditions