This post is part of a blog series which focuses on translating compatibility views in SQL Server to their respective dynamic management objects.  You can find a list of all of the deprecated views in the introduction post.

The compatibility view sysdepends returns rows dependency information for objects within a SQL Server database, such as establishing relationships between a stored procedure and the tables that it uses.  The information returned includes the dependent object information, along with column details, when applicable.  The compatibility view also determines whether the relationship results in SELECT, SELECT *, or update operations.  This easily allows DBAs to identify which objects will be affected, and how, by potential code changes.

Replacing sysdepends comes in the form of a couple of catalog views.  First there is sys.sql_dependencies; which returns results similar to sysdepends. Every row returned represents a relationship between two object in a SQL Server database.  The same general information is included for SELECT, SELECT *, and update operations.  The other catalog view is sys.sql_expression_dependencies; which provides similar information as the other catalog view but extends to three and four part object references.

Query Via sysdepends

There isn’t much that goes into writing a query against sysdepends.  There is a status column, but unlike other compatibility views, it doesn’t appear that anyone has managed to decipher anything of use out of that column.  There are a few other useless columns as well in the view; which include the number, depdbid, and depsiteid columns.  For the most part, queries will be similar to the one included in Listing 1.

Listing 1 – Query for sys.sysdepends
SELECT id
,depid
,number
,depnumber
,status
,deptype
,depdbid
,depsiteid
,selall
,resultobj
,readobj
FROM sysdepends

Query via sys.sql_dependencies

Using the sys.sql_dependencies; which is the first catalog view that maps to sysdepends, you are able to cover all of the valid functionality of the the compatibility view.  The columns which do not map into sysdepends are status, number, depdbid, and repsiteid.  All of these are either no longer used or undefined.  The query in Listing 2, provides the mapping of the catalog view to the compatibility, plus a couple additional columns not previously provided.  These columns are class_desc (translating the deptype) and column_id (the column that is referencing).

Listing 2 – Query for sys.sql_dependencies
SELECT
object_id AS id
,referenced_major_id AS depid
,NULL AS number
,referenced_minor_id AS depnumber
,NULL AS status
,class AS deptype
,class_desc
,NULL AS depdbid
,NULL AS depsiteid
,is_select_all AS selall
,is_updated AS resultobj
,is_selected AS readobj
,column_id
FROM sys.sql_dependencies

Query via sys.sql_expression_dependencies

The other catalog view that can be used in exchange of sysdepends is sys.sql_expression_dependencies.  As already mentioned, this view provides a deeper look into dependancies by providing a view into three and four part object names.  The information in the catalog view also misses on a few other areas, such as defining the relationships between select and update operations in the referencing objects.  While this is a gap, sys.sql_expression_dependencies isn’t aimed at that need, which is the purpose of the other view.  The purpose behind this view is to provide information on the full mapping of a table and column to a referencing object.  The query in Listing 3 shows how the columns in the compatibility view map to the catalog view.

Listing 3 – Query for sys.sql_expression_dependencies
SELECT referencing_id AS id
,referenced_id AS depid
,referenced_minor_id AS number
,referencing_minor_id AS depnumber
,NULL AS status
,NULL AS deptype
,NULL AS depdbid
,NULL AS depsiteid
,NULL AS selall
,NULL AS resultobj
,NULL AS readobj
,referencing_class
,referencing_class_desc
,is_schema_bound_reference
,referenced_class
,referenced_class_desc
,referenced_server_name
,referenced_database_name
,referenced_schema_name
,referenced_entity_name
,is_caller_dependent
,is_ambiguous
FROM sys.sql_expression_dependencies

The Dependency Rub

While this information can be very useful for tracking how objects relate to each other, there are a number of issues that can occur with the information.  And it all boils down to missing dependency information.  With the ability to create objects with deferred name resolution, changes to the names of columns, or objects being dropped, there are a number of situations where the dependency information on the catalog views are incorrect.  Rather than explaining it here, you should read this post from Thomas LaRock (Blog | @SQLRockstar) and this one by Aaron Bertrand (Blog | @AaronBertrand)

Summary

In this post, we discussed the use of sys.sql_dependencies and sys.sql_expression_dependencies over sysdepends.  Using sys.sql_dependencies to replace the compatibility view basically covers everything that is needed, while sys.sql_expression_dependencies can get you a little bit more.  After reading all of this, do you see any reason to continue using sysdepends?  Is there anything missing from this post that people continuing to use the compatibility view should know?

Follow me on Twitter at StrateSQL.

Original article: Lost in Translation – Deprecated System Tables – sysdepends

©2012 Strate SQL. All Rights Reserved.

Related posts:

1. Lost in Translation – Deprecated System Tables – sysdevices
2. Lost in Translation – Deprecated System Tables – sysconfigures
3. Lost in Translation – Deprecated System Tables – syscurconfigs