Troubleshooting SQL Dependencies
This topic describes common object dependency problems and their solutions.
Dynamic Management Function sys.dm_sql_referenced_entities Does Not Return Column-Level Dependencies
The sys.dm_sql_referenced_entities system function will report any column-level dependency for schema-bound references. For example, the function will report all column-level dependencies for an indexed view because an indexed view requires schema binding. However, when the referenced entity is not schema-bound, column dependencies are reported only when all statements in which the columns are referenced can be bound. Statements can be successfully bound only if all objects exist at the time the statements are parsed. If any statement defined in the entity fails to bind, column dependencies will not be reported and the referenced_minor_id column will return 0. When column dependencies cannot be resolved, error 2020 is raised. This error does not prevent the query from returning object-level dependencies.
Solution
Correct any errors identified in the message before error 2020. For example, in the following code example the view Production.ApprovedDocuments is defined on the columns Title, ChangeNumber, and Status in the Production.Document table. The sys.dm_sql_referenced_entities system function is queried for the objects and columns on which the ApprovedDocuments view depends. Because the view is not created using the WITH SCHEMA_BINDING clause, the columns referenced in the view can be modified in the referenced table. The example alters the column ChangeNumber in the Production.Document table by renaming it to TrackingNumber. The catalog view is queried again for the ApprovedDocuments view; however it cannot bind to all the columns defined in the view. Errors 207 and 2020 are returned identifying the problem. To resolve the problem, the view must be altered to reflect the new name of the column.
USE AdventureWorks;
GO
CREATE VIEW Production.ApprovedDocuments
AS
SELECT Title, ChangeNumber, Status
FROM Production.Document
WHERE Status = 2;
GO
SELECT referenced_schema_name AS schema_name
,referenced_entity_name AS table_name
,referenced_minor_name AS referenced_column
FROM sys.dm_sql_referenced_entities ('Production.ApprovedDocuments', 'OBJECT');
GO
EXEC sp_rename 'Production.Document.ChangeNumber', 'TrackingNumber', 'COLUMN';
GO
SELECT referenced_schema_name AS schema_name
,referenced_entity_name AS table_name
,referenced_minor_name AS referenced_column
FROM sys.dm_sql_referenced_entities ('Production.ApprovedDocuments', 'OBJECT');
GO
The query returns the following error messages.
Msg 207, Level 16, State 1, Procedure ApprovedDocuments, Line 3
Invalid column name 'ChangeNumber'.
Msg 2020, Level 16, State 1, Line 1
The dependencies reported for entity "Production.ApprovedDocuments" do not include references to 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.
The following example corrects the column name in the view.
USE AdventureWorks;
GO
ALTER VIEW Production.ApprovedDocuments
AS
SELECT Title,TrackingNumber, Status
FROM Production.Document
WHERE Status = 2;
GO
Column is_ambiguous Reports Inconsistent Values for User-Defined Functions
It may appear that the value reported in the column is_ambiguous is inconsistent for user-defined functions. The is_ambiguous column in the catalog view sys.sql_expression_dependencies and dynamic function sys.dm_sql_referenced_entities indicates that the reference to the entity is ambiguous. That is, the entity can resolve at run time to a user-defined function, a user-defined type (UDT), or an XQuery reference to a column of type xml. Depending on how the user-defined function is referenced, the entity type may or may not be clear, which can cause the is_ambiguous column to be set to 1 (true) in one case and to 0 (false) in another. For example, consider the following stored procedure.
CREATE PROCEDURE dbo.p1
AS
SELECT Sales.GetOrder() FROM t1;
SELECT Sales.GetOrder();
In the first SELECT statement, it is unclear whether Sales.GetOrder() is a user-defined function in the Sales schema or column named Sales of type UDT with a method named GetOrder(). In this case, the is_ambiguous column will be set to 1 for the referenced entity Sales.GetOrder(). In the second SELECT statement, the reference to Sales.GetOrder() is clear; based on the syntax, it can only be a reference to a user-defined function. In this case, the is_ambiguous column is set to 0. This behavior may make it appear that the value reported in the is_ambiguous column is inconsistent. Understanding how the value of the is_ambiguous column is determined can clarify the reported values.
The is_ambiguous column is set to 0 (false) when:
It is clear that the reference is to a user-defined function. That is, the query binds to a user-defined function and a column UDT method or column of type xml with that name does not exist.
It is clear that the reference is to a column UDT method. That is, a column with that UDT method exists and a user-defined function or column of type xml with the name does not.
The is_ambiguous column is set to 1 (true) when:
A user-defined function, column UDT method, or column of type xml with the referenced name does not exist.
The referenced name exists for multiple entities. For example, a user-defined function and a column UDT method have the same name.
For entities that are ambiguous in nature, it is possible that the referenced_database_name and referenced_schema_name columns are invalid. For example, consider the following user-defined function:
CREATE FUNCTION GetNextEmpHierarchyId (@empname varchar(25))
RETURNS hierarchyid
AS
BEGIN
RETURN
(
SELECT h.empid.GetDescendant((SELECT MAX(h1.empid)
FROM dbo.Employees AS h1
WHERE h1.empid.GetAncestor(1) = h.empid), NULL)
FROM dbo.Employees AS h
WHERE h.empname = @empname
)
END;
The columns referenced_database_name and referenced_schema_name will be invalid for the function due to the hierarchyid UDT method calls. It is not clear that references to h.empid.GetDescendant and h1.empid.GetAncestor are references an entity using a three-part name (database.schema.object) or to a UDT method (table.column.method).
Solution
No user action is required.
Column referenced_id Is Not Reported for Cross-Database Dependencies
The referenced_id column is never resolved for cross-database references in the sys.sql_expression_dependencies catalog view. The database name and schema name are recorded only when the name is explicitly specified. For example, when specified as MyDB.MySchema.MyTable, the database and schema names are recorded; however, when specified as MyDB..MyTable, only the database name is recorded.
The referenced_id is reported for cross-database references in the sys.dm_sql_referenced_entities system function only when the referenced entity can be successfully bound. Binding may fail for several reasons including the following causes:
The database is offline.
The referenced entity does not exist in the database.
Solution
Verify that the database is online and that the referenced entity exists in the database.
Column referenced_id is Null for Referenced Entities Within the Database
The sys.dm_sql_referenced_entities system function and the sys.sql_expression_dependencies system view will report the ID of any schema-bound referenced entity. However, the referenced_id column is NULL for non-schema-bound references within the database when the ID of the referenced entity cannot be determined. This can occur when:
The referenced entity does not exist in the database.
Name resolution is caller dependent. In this case, the is_caller_dependent column is set to 1.
Solution
Verify that the referenced entity exists in the database. Create the entity if it is not found, or, if the entity exists, make sure that the following requirements are met:
The referenced entity name is spelled correctly.
The specified name meets the collation requirements of the database. If the database uses a case-sensitive collation, the specified name specified must match the exact case of the object name. For example, the ID of an object named SalesHistory will not be found in a database with a case-sensitive collation if it is specified as saleshistory.
The schema name of the object is specified. A two-part name (schema_name.object_name) is required if the object is not in the default schema of the caller, the sys schema, or the dbo schema.
Modify the definition of the referencing entity to meet the above requirements.
If the referenced entity is caller dependent, modify the definition of the referencing entity by specifying a two-part name for the referenced entity. For more information about caller-dependent references, see Reporting SQL Dependencies.
Dependency Information Is Not Reported for Objects in the master Database
SQL dependencies on user-defined entities created in the master database are created and maintained. If SQL dependencies for an entity are not reported, follow these steps:
Ensure that the entity is a valid type for dependency tracking
Dependency information is not tracked for all user objects. For a list of entity types for which dependency information is created and maintained, see Understanding SQL Dependencies.
Ensure that the entity is not marked as a system object.
Query the is_ms_shipped column for the entity in the sys.objects catalog view. If this column is set to 1, the entity is either a system object that ships with SQL Server, or it is a user-defined object that has been modified to mimic a system object by manually setting this column to 1.
Solution
If the object is of a type that is not supported, dependency information will not be available.
Dependencies on system objects are not tracked. If the entity is user-defined, the is_ms_shipped column must be reset to 0 if you want SQL Server to create and maintain dependencies on the entity.