sys.sql_dependencies (Transact-SQL)
Contains a row for each dependency on a referenced (independent) entity as referenced in the SQL expression or statements that define some other referencing (dependent) object. The sys.sql_dependencies view is meant to track by-name dependencies between entities. For each row in sys.sql_dependencies, the referenced entity appears by-name in a persisted SQL expression of the referencing object. Additionally, if the referencing object has a schema-bound expression, the dependency is enforced. For more information, see Understanding SQL Dependencies.
| Column name | Data type | Description |
|---|---|---|
class |
tinyint |
Identifies the class of the referenced (independent) entity: 0 = Object or column (non-schema-bound references only) 1 = Object or column (schema-bound references) 2 = Types (schema-bound references) 3 = XML Schema collections (schema-bound references) 4 = Partition function (schema-bound references) |
class_desc |
nvarchar(60) |
Description of class of referenced (independent) entity:
|
object_id |
int |
ID of the referencing (dependent) object. |
column_id |
int |
If the dependent ID is a column, ID of referencing (dependent) column; otherwise, 0. |
referenced_major_id |
int |
ID of the referenced (independent) entity, interpreted by value of class, according to: 0, 1 = Object ID of object or column. 2 = Type ID. 3 = XML Schema collection ID. |
referenced_minor_id |
int |
Minor-ID of the referenced (independent) entity, interpreted by value of class, as shown in the following. When class =: 0, referenced_minor_id is a column ID; or if not a column, it is 0. 1, referenced_minor_id is a column ID; or if not a column, it is 0. Otherwise, referenced_minor_id = 0. |
is_selected |
bit |
Object or column is selected. |
is_updated |
bit |
Object or column is updated. |
is_select_all |
bit |
Object is used in SELECT * statement (object-level only). |
Remarks
Dependencies are established during CREATE only if the referenced (independent) entity exists at the time that the referencing (dependent) object is created. Due to deferred name resolution, the referenced entity need not exist at the time of creation. In this case, a dependency row is not created. Moreover, entities referenced through dynamic SQL do not establish dependencies.
If the referenced (independent) entity is dropped using DROP, the dependency row is deleted automatically. To re-establish the dependency row, you will need to re-create both, using CREATE, in the correct dependency order.
Both schema-bound and non-schema-bound dependencies are tracked for objects. CHECK constraints, defaults, and computed column references are implicitly schema-bound. Dependencies on types, XML schema collections, and partition functions are only tracked for schema-bound dependencies. In SQL Server 2005 Service Pack 1 and earlier, the parameters defined in a Transact-SQL function or procedure are implicitly schema bound. Therefore, parameters that depend on a CLR user-defined type, alias, or XML schema collection can be viewed by using the sys.sql_dependencies catalog view. However, this means that these objects cannot be renamed. In SQL Server 2005 Service Pack 2, parameters that depend on these objects are tracked in the catalog view only if the Transact-SQL module is created with schema binding. A CLR user-defined type, alias, or XML schema collection used as a parameter in a module that is not schema bound can be renamed, but the definition of the module may need to be refreshed by using sp_refreshsqlmodule.
Examples
A. Finding the dependencies on a specified function
The following example returns the dependencies on the specified function. Before you run the following query, replace <database_name> and <schema_name.function_name> with valid names.
USE <database_name>;
GO
SELECT OBJECT_NAME(object_id) AS referencing_object_name
,COALESCE(COL_NAME(object_id, column_id), '(n/a)') AS referencing_column_name
,*
FROM sys.sql_dependencies
WHERE referenced_major_id = OBJECT_ID('<schema_name.function_name>')
ORDER BY OBJECT_NAME(object_id), COL_NAME(object_id, column_id);
GO
B. Finding the CHECK constraints that depend on a specified CLR user-defined type
Before you run the following query, replace <database_name> with a valid name and <schema_name.data_type_name> with a valid, schema-qualified CLR user-defined type name.
USE <database_name>;
GO
SELECT SCHEMA_NAME(o.schema_id) AS schema_name
,OBJECT_NAME(o.parent_object_id) AS table_name
,OBJECT_NAME(o.object_id) AS constraint_name
FROM sys.sql_dependencies AS d
JOIN sys.objects AS o ON o.object_id = d.object_id
WHERE referenced_major_id = TYPE_ID('<schema_name.data_type_name>')
AND class = 2 -- schema-bound references to type
AND OBJECTPROPERTY(o.object_id, 'IsCheckCnst') = 1; -- exclude non-CHECK dependencies
GO
C. Finding the views, Transact-SQL functions, and Transact-SQL stored procedures that depend on a specified CLR user-defined type or alias type
The following query returns all schema-bound dependencies in views, Transact-SQL functions, and Transact-SQL stored procedures for a specified CLR user-defined type or alias type.
USE <database_name>;
GO
SELECT SCHEMA_NAME(o.schema_id) AS dependent_object_schema
,OBJECT_NAME(o.object_id) AS dependent_object_name
,o.type_desc AS dependent_object_type
,d.class_desc AS kind_of_dependency
,TYPE_NAME (d.referenced_major_id) AS type_name
FROM sys.sql_dependencies AS d
JOIN sys.objects AS o
ON d.object_id = o.object_id
AND o.type IN ('FN','IF','TF', 'V', 'P')
WHERE d.class = 2 -- dependencies on types
AND d.referenced_major_id = TYPE_ID('<schema_name.data_type_name>')
ORDER BY dependent_object_schema, dependent_object_name;
GO
See Also
Reference
Catalog Views (Transact-SQL)
Object Catalog Views (Transact-SQL)
sp_rename (Transact-SQL)
sp_refreshsqlmodule (Transact-SQL)
Other Resources
Implementing User-defined Types
Understanding SQL Dependencies
Querying the SQL Server System Catalog FAQ
Managing XML Schema Collections on the Server
Help and Information
Getting SQL Server 2005 Assistance
Change History
| Release | History |
|---|---|
12 December 2006 |
|