Understanding SQL Dependencies
SQL dependencies are the by-name references that are used in SQL expressions that make one entity reliant on another entity. An entity that references another entity in its definition and that definition is stored in the system catalog is called a referencing entity. An entity that is referred to by another entity is called a referenced entity. There are two types of dependency tracked by the Database Engine.
Schema-bound dependency
A schema-bound dependency is a relationship between two entities that prevents the referenced entity from being dropped or modified as long as the referencing entity exists. A schema-bound dependency is created when a view or user-defined function is created by using the WITH SCHEMABINDING clause. A schema-bound dependency can also be created when a table references another entity, such as a Transact-SQL user-defined function, user-defined type, or XML schema collection, in a CHECK or DEFAULT constraint or in the definition of a computed column. Specifying an object using a two-part (schema_name.object_name) name does not qualify as a schema-bound reference.
Non-schema-bound dependency
A non-schema-bound dependency is a relationship between two entities that does not prevent the referenced entity from being dropped or modified.
The following illustration shows an example of an SQL dependency.
.gif "Depiction of a SQL dependency")
In the illustration, there are two entities: procedure X and procedure Y. Procedure X contains an SQL expression that has a by-name reference to procedure Y. Procedure X is known as the referencing entity, and procedure Y is known as the referenced entity. Because procedure X depends on procedure Y, procedure X will fail with a run-time error if procedure Y does not exist. However, procedure Y will not fail if procedure X does not exist.
The following example shows how stored procedure X can depend on stored procedure Y.
USE tempdb;
GO
CREATE PROCEDURE dbo.Y AS
SELECT * FROM sys.objects
GO
CREATE PROCEDURE dbo.X as
EXEC dbo.Y;
GO
To see the dependency of X on Y, run the following query.
SELECT *
FROM sys.sql_expression_dependencies
WHERE referencing_id = OBJECT_ID('X')
AND referenced_id = OBJECT_ID('Y')
AND referenced_schema_name = 'dbo'
AND referenced_entity_name = 'Y'
AND referenced_database_name IS NULL
AND referenced_server_name IS NULL;
GO
Types of Referencing and Referenced Entities
The following table lists the types of entities for which dependency information is created and maintained. The table indicates whether the entity is tracked as a referencing entity or a referenced entity. Dependency information is not created or maintained for rules, defaults, temporary tables, temporary stored procedures, or system objects.
Entity type |
Referencing entity |
Referenced entity |
|---|---|---|
Table |
Yes* |
Yes |
View |
Yes |
Yes |
Transact-SQL stored procedure** |
Yes |
Yes |
CLR stored procedure |
No |
Yes |
Transact-SQL user-defined function |
Yes |
Yes |
CLR user-defined function |
No |
Yes |
CLR trigger (DML and DDL) |
No |
No |
Transact-SQL DML trigger |
Yes |
No |
Transact-SQL database-level DDL trigger |
Yes |
No |
Transact-SQL server-level DDL trigger |
Yes |
No |
Extended stored procedures |
No |
Yes |
Queue |
No |
Yes |
Synonym |
No |
Yes |
Type (alias and CLR user-defined type) |
No |
Yes |
XML schema collection |
No |
Yes |
Partition function |
No |
Yes |
* A table is tracked as a referencing entity only when it references a Transact-SQL module, user-defined type, or XML schema collection in the definition of a computed column, CHECK constraint, or DEFAULT constraint.
** Numbered stored procedures with an integer value greater than 1 are not tracked as either a referencing or referenced entity.
How Dependency Information Is Tracked
The Database Engine automatically tracks dependency information when referencing entities are created, altered, or dropped and records this information in the SQL Server system catalog. For example, if you create a trigger that references a table, a dependency between these entities is recorded. If you subsequently drop the trigger, the dependency information is removed from the system catalog.
Unlike earlier versions of SQL Server, in which dependencies were tracked by ID, dependencies are now tracked by name. This means that the Database Engine tracks dependency information between two entities even if the referenced entity does not exist at the time the referencing entity is created. This circumstance can occur because of deferred name resolution. For example, a stored procedure that references a table can be successfully created, but not executed, even if the referenced table does not exist in the database. The Database Engine records the dependency between the procedure and table, however, an ID for the table cannot be recorded because the object does not yet exist. If the table is later created, the ID of the table is returned with the other dependency information.
Dependency information is tracked when the referenced entity appears by name in a persisted SQL expression of the referencing entity. Dependency information is obtained when entities are referenced by name in the following ways:
By using any of the following statements in the definition of a Transact-SQL module:
Data Manipulation Language (DML) statements (SELECT, INSERT, UPDATE, DELETE, MERGE)
EXECUTE
DECLARE
SET (When SET is used with a user-defined function or user-defined type. For example, DECLARE @var int; SET @var = dbo.udf1.)
Entities referenced in the definition of a Transact-SQL module by using Data Definition Language (DDL) statements such as CREATE, ALTER, or DROP are not tracked.
By using CREATE, ALTER, or DROP TABLE statements when the statements are not in a Transact-SQL module and the referenced entity is a Transact-SQL user-defined function, user-defined type, or XML schema collection defined in a computed column, CHECK constraint, or DEFAULT constraint.
Cross-Database and Cross-Server Dependencies
A cross-database dependency is created when an entity makes a reference to another entity by using a valid three-part name. A cross-server reference is created when an entity makes a reference to another entity by using a valid four-part name. The name of the server and database are recorded only when the name is explicitly specified. For example, when specified as MyServer.MyDB.MySchema.MyTable, the server and database names are recorded; however, when specified as MyServer..MySchema.MyTable, only the server name is recorded. For information about valid multipart names, see Transact-SQL Syntax Conventions (Transact-SQL).
The following limitations apply:
Cross-server dependencies for OPENROWSET, OPENQUERY, and OPENDATASOURCE statements are not tracked.
Dependencies for the statement EXEC ('…') AT linked_server are not tracked.
The following table summarizes the cross-server and cross-database dependencies that are tracked and the information that is recorded in the system catalog and reported by sys.sql_expression_dependencies (Transact-SQL).
SQL expression in a module |
Is tracked |
Referenced server name |
Referenced database name |
Referenced schema name |
Referenced entity name |
|---|---|---|---|---|---|
SELECT * FROM s1.db2.sales.t1 |
Yes |
s1 |
db2 |
sales |
t1 |
SELECT * FROM db3..t1 |
Yes |
|
db3 |
|
t1 |
EXEC db2.dbo.Proc1 |
Yes |
|
db2 |
dbo |
proc1 |
EXEC ('…') AT linked_srv1 |
No |
|
|
|
|
EXEC linked_svr1.db2.sales.proc2 |
Yes |
linked_svr1 |
db2 |
sales |
proc2 |
Effect of Collation on Dependency Tracking
A collation determines the rules that sort and compare data. The collation of the database is used to identify dependency information for entities within the database. For example, if a stored procedure references the entities Some_Table and SOME_TABLE in a database that uses a case-sensitive collation, dependency information for two entities is recorded because a comparison of the two names indicates that they are not the same. If the database uses a case-insensitive collation, however, only a single dependency is recorded.
For cross-server and cross-database dependencies, the collation of the server on which the referencing object resides is used to resolve the name of the server and database. The collation of the current database is used to resolve the name of the schema and object names.
Consider the following stored procedure definition. If the stored procedure is created in a database with a case sensitive collation on an instance of SQL Server with a case-insensitive server collation, two dependencies are recorded for the entities srv_referenced.db_referenced.dbo.p_referenced and srv_referenced.db_referenced.DBO.P_REFERENCED.
CREATE PROCEDURE p_referencing AS
EXECUTE srv_referenced.db_referenced.dbo.p_referenced
EXECUTE srv_referenced.db_referenced.DBO.P_REFERENCED
EXECUTE SRV_REFERENCED.DB_REFERENCED.dbo.p_referenced;
Resolving Ambiguous References
A reference is ambiguous when it 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.
Consider the following stored procedure definition.
CREATE PROCEDURE dbo.p1 AS
SELECT column_a, Sales.GetOrder() FROM Sales.MySales;
At the time the stored procedure is created, it is not known whether Sales.GetOrder() is reference to a user-defined function named GetOrder in the Sales schema or a column named Sales of type UDT with a method named GetOrder(). When a reference is ambiguous, the dependency is reported as being ambiguous by setting the is_ambiguous column in sys.sql_expression_dependencies and sys.dm_sql_referenced_entities to 1. The following dependency information is reported:
The dependency between the stored procedure and the table.
The dependency between the stored procedure and the user-defined function. If the function exists, the ID of the function is reported; otherwise, ID is NULL.
The dependency on the function is marked as ambiguous. That is, is_ambiguous is set to 1.
Column-level dependencies are not reported because the statement in which the columns are referenced cannot be bound.
Maintaining Dependencies
The Database Engine maintains both schema-bound and non-schema-bound dependencies. These dependencies are automatically refreshed during any operation that impacts dependency tracking, for example when upgrading a database from an earlier version of SQL Server or changing the collation of a database.