Finding SQL Server Object Dependencies with DMVs

Problem

In a previous tip we have shared scripts that could be useful for finding object dependencies during an application development or databases upgrades. Further scripts testing showed that not all of the dependencies were displayed in some scenarios. What did we miss? What should you know about the sys.sql_expression_dependencies DMV (Dynamic Management View)?

Solution

In this tip we will provide you updated scripts to find the dependencies. We will also demonstrate a well known best practice that should be followed to avoid missing dependencies.

Note, that by saying “missing dependencies” we mean that their object IDs are not showing up in the sys.sql_expression_dependencies DMV.

Missing Object ID in sys.sql_expression_dependencies DMV

To demonstrate the missing dependencies in the sys.sql_expression_dependencies DMV let’s run a setup script below first:

 -- Demo Setup
USE [_DemoDB]
GO
CREATE PROC dbo.p1 AS SELECT 1 AS Col1;
GO
CREATE PROC dbo.p2 AS EXEC dbo.p1;
GO
CREATE PROC dbo.p3 AS EXEC p1; -- note that schema name is missing for p1
GO
CREATE VIEW dbo.v1 AS SELECT 1 AS Col1;
GO
CREATE VIEW dbo.v2 AS SELECT * FROM dbo.v1;
GO
CREATE VIEW dbo.v3 AS SELECT * FROM v1; -- note that schema name is missing for p1
GO

Note that in the p3 stored procedure and v3 view we have not specified a schema for the referenced objects (p1 and v1).

Now we will run the script that was provided in the previous tip to find the dependencies:

 USE [_DemoDB]
GO
WITH DepTree (referenced_id, referenced_name, referencing_id, referencing_name, NestLevel)
 AS 
(
    SELECT  o.[object_id] AS referenced_id , 
      o.name AS referenced_name, 
      o.[object_id] AS referencing_id, 
      o.name AS referencing_name,  
      0 AS NestLevel
    FROM  sys.objects o 
 WHERE o.type  IN ('P', 'V') AND o.is_ms_shipped = 0 -- you may need to add more filters if you exceed MAXRECURSION limit
    
    UNION ALL
    
    SELECT  d1.referenced_id,  
      OBJECT_NAME( d1.referenced_id) , 
      d1.referencing_id, 
      OBJECT_NAME( d1.referencing_id) , 
      NestLevel + 1
     FROM  sys.sql_expression_dependencies d1 
   JOIN DepTree r ON d1.referenced_id =  r.referencing_id
)
SELECT DISTINCT referenced_id, referenced_name, referencing_id, referencing_name, MAX(NestLevel) NestLevel
 FROM DepTree  
 GROUP BY referenced_id, referenced_name, referencing_id, referencing_name
 HAVING  MAX(NestLevel) > 0
ORDER BY  referencing_id
OPTION (MAXRECURSION 1000); -- you may need to increase MAXRECURSION limit up to 32,767 if you have too high nest levels or too many objects

The results are below:

Wait a minute… where is the p3 stored procedure that we created with the setup script?

Check the content of the sys.sql_expression_dependencies DMV:

 USE [_DemoDB]
GO
SELECT OBJECT_NAME(referencing_id) AS referencing_entity_name, 
 referencing_id,
 referenced_schema_name, 
 referenced_entity_name,
 referenced_id, 
 is_caller_dependent 
FROM    sys.sql_expression_dependencies;
GO

The result has NULL for the p3 referenced_id:

Query results - sys.sql_expression_dependencies DMV

You would think that joining by object_id is the best way when you write the scripts, but not in this case. That’s one of the reasons why this record was missed in the initial dependencies check query.

Note, that referenced_schema_name column has NULLs for both – p3 and v3 (remember the missing schema in the procedure/view body?).

Why is this happening?

As per Microsoft’s documentation of the sys.sql_expression_dependencies DMV:

referenced_id – “…The schema of the referenced entity depends on the schema of the caller and is resolved at run time. In this case, is_caller_dependent is set to 1.“
is_caller_dependent – “Indicates that schema binding for the referenced entity occurs at runtime; therefore, resolution of the entity ID depends on the schema of the caller. This occurs when the referenced entity is a stored procedure, extended stored procedure, or a non-schema-bound user-defined function called in an EXECUTE statement. 1 = The referenced entity is caller dependent and is resolved at runtime. In this case, referenced_id is NULL. 0 = The referenced entity ID is not caller dependent. Always 0 for schema-bound references and for cross-database and cross-server references that explicitly specify a schema name. For example, a reference to an entity in the format EXEC MyDatabase.MySchema.MyProc is not caller dependent. However, a reference in the format EXEC MyDatabase..MyProc is caller dependent.“

To apply schema binding that happens at runtime we need to add a default user’s schema to our queries to find dependencies:

 USE [_DemoDB]
GO
SELECT 
 OBJECT_NAME(d1.referencing_id) AS referencing_entity_name, 
 d1.referencing_id,
 d1.referenced_schema_name, 
 d1.referenced_entity_name,
 d1.referenced_id, 
 OBJECT_ID(ISNULL(d1.referenced_schema_name, dp.default_schema_name) + '.' + d1.referenced_entity_name) AS referenced_id_new, 
 d1.is_caller_dependent 
FROM  sys.sql_expression_dependencies d1  
  CROSS JOIN  sys.database_principals dp
WHERE dp.[name] = USER_NAME();
GO

Now we see the dependent object’s ID in the referenced_id_new column:

Query results - sys.sql_expression_dependencies with new column

We can now add a user’s schema to our dependencies check query and review the results:

 -- updated dependencies check query. Use it later for our next tests below
USE [_DemoDB]
GO
WITH DepTree 
 AS 
(
    SELECT  o.[object_id] AS referenced_id , 
 SCHEMA_NAME(o.schema_id) referenced_schema_name,
 o.name AS referenced_name, 
 o.[object_id] AS referencing_id, 
 o.name AS referencing_name,  
 0 AS NestLevel
    FROM  sys.objects o 
    WHERE o.type  IN ('P', 'V') AND o.is_ms_shipped = 0 -- you may need to add more filters if you exceed MAXRECURSION limit
    
    UNION ALL
    
    SELECT  ISNULL(d1.referenced_id, OBJECT_ID(ISNULL(d1.referenced_schema_name, dp.default_schema_name) + '.' + d1.referenced_entity_name)) referenced_id, 
 ISNULL(d1.referenced_schema_name, dp.default_schema_name) AS referenced_schema_name,
 d1.referenced_entity_name AS referenced_name,
 d1.referencing_id, 
 OBJECT_NAME( d1.referencing_id) referencing_name, 
 NestLevel + 1
     FROM  sys.sql_expression_dependencies d1 CROSS JOIN  
       sys.database_principals dp JOIN 
       DepTree r ON  r.referencing_id =  ISNULL(d1.referenced_id, OBJECT_ID(ISNULL(d1.referenced_schema_name, default_schema_name) + '.' + d1.referenced_entity_name)) 
 WHERE dp.[name] = USER_NAME()
)
SELECT DISTINCT referenced_id, 
  referenced_name, 
  referencing_id, 
  referencing_name, 
  MAX(NestLevel) NestLevel
 FROM DepTree  
 GROUP BY referenced_id, referenced_name, referencing_id, referencing_name
 HAVING  MAX(NestLevel) > 0
ORDER BY  referencing_id
OPTION (MAXRECURSION 1000);  -- you may need to increase MAXRECURSION limit up to 32,767 if you have too high nest levels or too many objects
GO

The result is now returning four records as expected:

Query results - updated dependencies query

Note, that this works for the objects that are in the same schema that is the user’s default schema. If there are objects with the same name in different schemas the results will be different.

Note, that if you still get an error about exceeding MAXRECURSION limit you may need to re-write CTE query and use temp tables.

Testing with a different user that has non-dbo default schema

Let’s create a test user (without a login) with a different default schema (not dbo):

 USE [_DemoDB]
GO
CREATE SCHEMA [TestDBO_schema] AUTHORIZATION [dbo]
GO
-- note the DEFAULT_SCHEMA for the user
CREATE USER [TestDBO] WITHOUT LOGIN WITH DEFAULT_SCHEMA=[TestDBO_schema]
GO
ALTER ROLE [db_owner] ADD MEMBER [TestDBO]
GO

We have created a user without login, but you can create a traditional login with either Windows or SQL Server Authentication.

To run our test – set the execution context to TestDBO user and run the updated dependencies check query under the same session:

 USE [_DemoDB]
GO
-- set execution context to TestDBO user
EXECUTE AS USER = 'TestDBO';
GO
-- Run the updated dependencies check query using the script above

Even though we have updated our query this new user still doesn’t see the p3 stored procedure. This is because the default schema of the user is TestDBO_schema and this schema is used for this user at runtime for the objects referenced without a schema name:

Now we will create a stored procedure with the same name (p1) in the TestDBO_schema schema and will run the updated dependencies check query again. We will run the check query first as the TestDBO user and then as dbo:

 USE [_DemoDB]
GO
-- set execution context to TestDBO user
EXECUTE AS USER = 'TestDBO';
GO
-- create stored procedure under TestDBO schema
CREATE PROC TestDBO_schema.p1 AS SELECT 2 AS Col2;
GO
-- Run the updated dependencies check query using the script above
-- set execution context to TestDBO user
EXECUTE AS USER = 'dbo';
GO
-- Run the updated dependencies check query using the script above

Under the results below you can see that second row has a different referenced_id when we ran the query as dbo and as TestDBO user:

Query results - ran updated dependencies query as different users

These are actually references to different objects.

Best Practices

Make sure you understand how to use the sys.sql_expression_dependencies DMV and what each column displays.

There is a perfect way to make it straight forward. Remember the best practice is to always use a schema name with object name inside stored procedures, views and functions. This best practice is usually linked to performance, but you can see that it’s important for other reasons as well.

To fix the issue of missing dependencies we will update p3 stored procedure and run the check query again as dbo and then as TestDBO user:

 USE [_DemoDB]
GO
ALTER PROC [dbo].[p3] AS EXEC dbo.p1; -- updated "p1" to "dbo.p1"
GO
-- set execution context to TestDBO user
EXECUTE AS USER = 'dbo';
GO
-- Run the updated dependencies check query using the script above
-- set execution context to TestDBO user
EXECUTE AS USER = 'TestDBO';
GO
-- Run the updated dependencies check query using the script above

Now the results of the dependency check query are the same for both users:

Query results - ran updated dependencies query as different users after fixing procedure

Check your Code with this Query

If you perform code review or want to fix the issue described above you might find the next query useful. It will save you a lot of time as you don’t have to review code line by line. The query will find procedures where objects are referenced without the schema name:

 USE [_DemoDB]
GO
SELECT 
 OBJECT_NAME(d1.referencing_id) AS referencing_entity_name, 
 d1.referenced_entity_name
 FROM  sys.sql_expression_dependencies d1  
WHERE d1.referenced_schema_name IS NULL 
 AND d1.referenced_id IS NULL
 AND d1.is_caller_dependent = 1
ORDER BY  OBJECT_NAME(d1.referencing_id);
GO

Next Steps

Document existing dependencies.
Read Different Ways to Find SQL Server Object Dependencies – Part 1 tip.
Read Different Ways to Find SQL Server Object Dependencies – Part 2 tip.
In this tip, there are several methods to list object dependencies that were used in previous versions of SQL Server.
Check missing dependencies and find out which procedures don’t follow the best practices.
Remember to always use schema names in stored procedures, views and functions.
Review the SQL Server Best Practices Implementation of Database Object Schemas technical article from Microsoft explaining the concept of a database object schema. At the bottom of the article you can find “Conclusion” with the best practices.

Resources

Community

Subscribe