Understanding SQL Dependencies

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 For server-level triggers, see sys.server_sql_modules Note:	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