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.

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.