Revisiting Object Dependencies

Last week we looked at identifying object dependencies through performing a wildcard search on the objects such as procedures. I noted another option could be to use the views such as sys.sql_expression_dependencies and Kevin also commented about using the supporting functions such as sys.dm_sql_referencing_entities.

I wanted to briefly look at both of these options and look at how their results can differ. As ever lets set up an example to work with:

CREATE TABLE dbo.NowYouSeeIt (
	ID INT PRIMARY KEY,
	Val VARCHAR(20)
);
GO

CREATE PROC dbo.GetFirstID AS
BEGIN
	SELECT TOP (1) ID, Val
	FROM dbo.NowYouSeeIt;
END

Using the functions

When using the Dynamic Management Functions (DMFs) we have two options:

• sys.dm_sql_referenced_entities will query to see which objects are referenced by the one we pass into the function. Generally what you could surmise through reading a procedure.
• sys.dm_sql_referencing_entities will query to see which objects reference the one passed into the function. Typically more useful as you can see what is hitting a specific table for example.

Looking at the first DMF above and passing in our procedure name to see what is referenced:

SELECT *
FROM sys.dm_sql_referenced_entities('dbo.GetFirstID', 'object');

There’s a lot of good detail in here, I’ve snipped a selection of the columns down. You can see it not only shows what object we’ve referenced but also what server and database that resides on. Even more granular we can see the columns referenced. Finally we can even use this to identify how the table is used through a SELECT and even if SELECT * is used to retrieve the columns.

Next up we’ll see what objects are referencing our table:

SELECT *
FROM sys.dm_sql_referencing_entities('dbo.NowYouSeeIt', 'object');

This function provides much less detail, but I would typically find more useful when considering lineage and dependencies through a database. We can see the object which has referenced the table – our procedure – but without the same level of granularity to understand how it’s used.

I like these functions due to their accuracy in the results. This can also be their downfall however.

It would appear that the functions evaluate the dependencies on execution. This means that if object references aren’t schema bound then they may become invalid. This causes an issue for our functions:

/* Hide the table */
EXEC sp_rename 'dbo.NowYouSeeIt', 'NowYouDont';

/* Now we try again */
SELECT *
FROM sys.dm_sql_referenced_entities('dbo.GetFirstID', 'object');

The dependencies reported for entity “dbo.GetFirstID” might not include references to all columns. This is either because the entity references an object that does not exist or because of an error in one or more statements in the entity. Before rerunning the query, ensure that there are no errors in the entity and that all objects referenced by the entity exist.

Its a slightly different story with the referencing function:

SELECT *
FROM sys.dm_sql_referencing_entities('dbo.NowYouSeeIt', 'object');

SELECT *
FROM sys.dm_sql_referencing_entities('dbo.NowYouDont', 'object');

With these we won’t see an error but we also won’t get results from either of them.

This approach is accurate to what is currently deployed, however can be vulnerable to throwing up errors and returning nothing at all if we’ve had movement in our schemas.

Let’s take a look at the other approach.

Using the views

When using the Dynamic Management Views (DMVs) to query the reference data it will return all references within the database. This will provide more visibility in a single query and we can filter down if required.

SELECT ReferencingObject = OBJECT_NAME(referencing_id), *
FROM sys.sql_expression_dependencies;

Again I’ve reduced the columns down here and we can see the objects which are referencing and those which are referenced too. This is similar to the ‘referenced’ function above however it doesn’t present details down to a column level. Those details are still available in another view though:

SELECT ReferencingObject = OBJECT_NAME(object_id),
	ReferencedObject = OBJECT_NAME(referenced_major_id),
	*
FROM sys.sql_dependencies;

Again these are a snip of some columns in the results. We have similar columns to the earlier function, albeit we need to identify the objects and columns ourselves as they aren’t as explicit.

There is a very key difference here however. I haven’t renamed the table back. You can see the first view states the old name and the second view shows the new name (as its referenced by ID).

The stored procedure hasn’t been modified so the statement inside is still referencing the old name. A bit of a mess we have here.

This metadata is taken when the procedure was last deployed so it knows what the referenced objects were back then. We’ve since renamed it so can still find the referenced object. If the object had been removed however we’d simply have orphan data.

Whilst this approach doesn’t provide as accurate details as the functions, its more robust in that it’ll provide a full set of references, but some of which may not be wholly accurate.

Wrap up

Here we’ve followed on from the previous post where we used text search to find dependencies to look at object references identified by the engine.

We’ve looked at two different approaches:

• Using mangagement functions: the details returned are accurate but can throw up challenges if there have been changes to schema and references haven’t been updated
• Using management views: whilst not as accurate it can be more resilient to return at least some details if there have been schema movements, and does provide visibility across the whole database in one location

Both methods have their different benefits and drawbacks. On top of those, neither of them will be identifying potential references in dynamic SQL as it won’t be parsed when deploying.

There really is no right answer here. I like the string search as a first point of call as it’ll cover dynamic SQL and more up to date references, but for common terms it falls down heavily.