Learn more about SQL Server tools

mssqltips logo
giveaway
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Finding SQL Server Object Dependencies with DMVs


By:   |   Read Comments (6)   |   Related Tips: 1 | 2 | 3 | 4 | 5 | More > Comparison Data and Objects


ALERT: Share your SQL Server knowledge and become a MSSQLTips author


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:

Query results - missing row

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:

Query results - missing row

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


Last Update:


signup button

next tip button



About the author





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



Friday, May 26, 2017 - 1:38:25 PM - Svetlana Golovko Back To Top

 Updated the CTE queries in the tip to include both - views and procedures to match the screenshots: "WHERE o.type IN ('P', 'V') "


Friday, May 26, 2017 - 8:27:32 AM - Richard Back To Top

 Thanks for updating the queries. It's woring fine now.

For sake of the examples in the article I would suggest to filter on both Procedures and Views. So WHERE o.type  IN ('V','P')  instead ofWHERE o.type  = 'P'

 


Friday, May 26, 2017 - 5:33:18 AM - Kingston Back To Top

 

 Hello Svetlana,

                    Very usefule post !. Is there a way to find the dependency of a given column ?

 

 


Thursday, May 25, 2017 - 11:30:46 PM - Svetlana Golovko Back To Top

 Two CTE queries have been updated in this tip to minimize possibility of maximum recursion error:

  • added filter to exclude system objects and only include procedures object type (the screenshots have both - procedures and views)
  • added "OPTION (MAXRECURSION 1000) "

You may need to increase MAXRECURSION up to the maximum (32,767). Also, you may need to re-write CTE queries and replace them with temp tables if you still have this error: "Msg 530, Level 16, State 1 The statement terminated. The maximum recursion 32,767 has been exhausted before statement completion."

Thanks Richard for testing the queries.


Thursday, May 25, 2017 - 10:35:21 AM - Svetlana Golovko Back To Top

 

 Hi Richard,

 

You may need to limit the number of objects checked and exclude system objects in CTE:

.....

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  = 'P' and o.is_ms_shipped = 0 -- add filter to limit number of recursions

.....

If you still get this error you may need to increase gradually the maximum recursion limit at the end of the statement (up to 32,767):

 

....

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); -- add this line to increase the maximum recursion limit 


Thursday, May 25, 2017 - 7:44:58 AM - Richard Back To Top

When using it with my own (small) databases I keep getting this error.

 Msg 530, Level 16, State 1 The statement terminated. The maximum recursion 100 has been exhausted before statement completion.

 

 


Learn more about SQL Server tools