Using object dependencies in SQL Server 2008

Previous versions of SQL Server did not do a good job of tracking object dependencies. The reason is that all object dependencies are tracked by object IDs, which means that the object must be present at the outset. However, tracking objects by object name, SQL Server 2008 greatly expands the ability to trace objects without having to rely on IDs. The advantage of this approach is that after some objects have been moved from the database, they can also continue to trace objects, even when they are not established.

New Management Objects

SQL Server 2008 introduces two new dynamic management functions and a new system view for tracking object dependencies. These two new objects include information about dependencies that are stored by the database engine, which are established when objects are created, changed, and discarded. When an object appears as a name in an SQL expression stored in another object, a dependency is created between two objects. An object that appears in an expression is called a referenced entity, and an object that contains an SQL expression is called a reference entity.

Sys.sql_expression_dependencies

This view contains a record of each dependency of a user-defined object in the current database. These user-defined objects can be objects stored in the current database, or objects that are stored in different databases and referenced by a partial naming convention (Databasename.schemaname.objectname), and use the four-part naming convention on different servers ( Servername.databasename.schemaname.objectname objects that are referenced through linked servers, and objects that do not exist when a particular object is created (called deferred objects).

Sys.dm_sql_referenced_entities

When a user defines a specific reference entity, this function returns a row for each user-defined object referenced by name, for example, if the view Vw_sampleview references the Field1, Field2, and Field3 of the table Table1, it returns four rows, returning three rows for the referenced field. Returns a row for a table reference.

Sys.dm_sql_referencing_entities

This function returns a record for each object defined by the user in the current database that references another user-defined object by name. For example, if the view Vw_sampleview references Table1 and Table2, the function returns two records that return a record for each table reference.

Example

The following example shows how to track dependencies within a database by creating objects and querying the new DMV. Use the following script to create a table with the name Saleshistory:

CREATE TABLE [dbo].[SalesHistory](
[SaleID] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED,
[Product] [char](150) NULL,
[SaleDate] [datetime] NULL,
[SalePrice] [money] NULL
)

In the following script, a procedure named Dbo.usp_getsales is created that references the Saleshistory table created in the previous script:

CREATE PROCEDURE dbo.usp_GetSales
(
@Product VARCHAR(10)
)
AS
BEGIN
SELECT COUNT(SaleID) AS SalesCount, SUM(SalePrice) AS SalesAmount
FROM dbo.SalesHistory sh
END

The script below uses the table-valued function number Sys.dm_sql_referenced_returns to find all objects referencing the Saleshistory table. This query not only returns a reference to the Saleshistory table, but also contains fields from the referenced saleshistory table. It is worth noting the use of the cross apply operator, which is because the entity is a table-valued function and requires the Apply operator when assigning a field value to a table-valued function. Also note that if you encounter a reference to an entity field that does not exist, the call to the Sys.dm_referenced_entities function can be faulted:SELECT ReferencedEntityName = o.name, g.referenced_entity_name, referenced_minor_name
FROM sys.objects o
JOIN sys.schemas s on o.schema_id = s.schema_id
CROSS APPLY sys.dm_sql_referenced_entities(s.name + '.' + o.name, 'OBJECT') g
WHERE referenced_entity_name = 'SalesHistory'

In the script below, a new stored procedure named Usp_getsalesfromarchive is created that references a saleshistoryarchive table that does not exist

CREATE PROCEDURE usp_GetSalesFromArchive
(
@Product VARCHAR(10)
)
AS
BEGIN
SELECT COUNT(SaleArchiveID) AS SalesCount, SUM(SaleArchivePrice) AS SalesAmount
FROM dbo.SalesHistoryArchive sh
END

You can now look for any object referencing the Saleshistoryarchive table using the Sys.sql_expression_dependencies system view.

Alternatively, you can change the query slightly to find the stored procedure usp_getsalesfromarchive the referenced table.

SELECT ReferencingObject = o.name ,
ReferencedObject = d.referenced_entity_name
FROM sys.sql_expression_dependencies d
join sys.objects o on d.referencing_id = o.object_id
WHERE o.name = 'usp_GetSalesFromArchive'

Conclusion

Tracking dependencies by object names rather than object IDs has many benefits. A user might like to look for a stored procedure that references an object that does not exist because it helps the user find any stored procedures that are no longer in use or need to be updated.