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 Y
.
USE tempdb
GO
CREATE PROCEDURE Y AS
SELECT * FROM sys.objects
GO
CREATE PROCEDURE X as
EXEC Y
GO
To see the dependency of X
on 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
. 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 Y
.
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.
Note
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
sys.sql_modules
Note:
For server-level triggers, see sys.server_sql_modules
Table, procedure, type
Computed column
sys.computed_columns
Function, type, another column
DEFAULT definition
sys.default_constraints
Function
CHECK constraint
sys.check_constraints
Function, partition function
Numbered procedure
sys.numbered_procedure
Table, procedure, type
Schema-bound function
sys.sql_modules
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 |
---|---|---|
Column |
Table |
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 |
See Also
Other Resources
sys.sql_dependencies (Transact-SQL)
sys.sysdepends (Transact-SQL)
sp_depends (Transact-SQL)