Understanding SQL Dependencies
New: 5 December 2005
SQL dependencies are the by-name references that are used in SQL expressions that make one object dependent on another object. You can find all by-name dependencies by querying the sys.sql_dependencies catalog view. For each row in sys.sql_dependencies, the referenced entity (referenced_major_id) appears by name in a persisted SQL expression of the referencing object.
The following illustration shows an example of an SQL dependency.
In the illustration, there are two objects: 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 or dependent object, and procedure Y is known as the referenced or independent object. 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
USE tempdb GO CREATE PROCEDURE Y AS SELECT * FROM sys.objects GO CREATE PROCEDURE X as EXEC Y GO
To see the dependency of
Y, run the following query.
SELECT * FROM sys.sql_dependencies WHERE object_id = object_id('X') AND referenced_major_id = object_id('Y') AND referenced_minor_id = 0 AND class = 0; GO
If you drop procedure
X, you can still run procedure
Y is independent of procedure
X. Conversely, if you drop procedure
Y and try to run procedure
X, SQL Server returns a run-time error. Procedure
X depends on procedure
After you drop procedure
Y, the row for procedure
X in the sys.sql_dependencies catalog view is also dropped. To see this behavior, run the following code.
USE tempdb; GO DROP PROCEDURE Y; GO SELECT * FROM sys.sql_dependencies WHERE object_id = object_id('X') AND referenced_major_id = object_id('Y') AND referenced_minor_id = 0 AND class = 0; GO
You can use the WITH SCHEMABINDING clause to enforce dependency maintenance. If you schema bind a view, you cannot modify or drop the referenced table or columns in a way that causes the dependency to break. Similarly, if you schema bind a function, you cannot modify or drop the referenced objects and columns in a way that breaks the dependencies of the function. For more information about schema binding, see Creating User-defined Functions (Database Engine) and Designing and Implementing Views.
SQL Server 2005 does not support schema-bound stored procedures or triggers.
Examples of SQL Dependencies
The following table lists some examples of SQL dependencies that can exist between referencing and referenced objects.
Type of referencing object
SQL expression in catalog-view definition column
Referenced entity example
Procedure, function, view, trigger
For server-level triggers, see sys.server_sql_modules
Table, procedure, type
Function, type, another column
Function, partition function
Table, procedure, type
XML schema collection
Examples of Non-SQL Dependencies
Not all dependencies that exist between objects are SQL dependencies. If an object does not have an SQL expression that contains a by-name reference to another object, an SQL dependency does not exist between the two objects. The following table lists some examples of non-SQL dependencies.
|Type of referencing object||Depends on||Description|
The dependency between the table and its columns is implicit. This relationship is expressed in the system catalog as the foreign key sys.columns.object_id.
DEFAULT definition on a column, for example:
Column. From the example,
No SQL dependency exists between the DEFAULT definition and the column because the default