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: CREATE TABLE myTable AS (ColA int CONSTRAINT Const1 DEFAULT 1)	Column. From the example, ColA.	No SQL dependency exists between the DEFAULT definition and the column because the default Const1 does not refer to column ColA by name. Instead, there is a foreign key reference from sys.columns.default_object_id to the DEFAULT definition object.

See Also

Other Resources

sys.sql_dependencies (Transact-SQL)
sys.sysdepends (Transact-SQL)
sp_depends (Transact-SQL)

Help and Information

Getting SQL Server 2005 Assistance