Export (0) Print
Expand All
Expand Minimize
1 out of 7 rated this helpful - Rate this topic

sys.sql_dependencies (Transact-SQL)

Updated: 12 December 2006

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_OR_COLUMN_REFERENCE_NON_SCHEMA_BOUND
  • OBJECT_OR_COLUMN_REFERENCE_SCHEMA_BOUND
  • TYPE_REFERENCE
  • XML_SCHEMA_COLLECTION_REFERENCE
  • PARTITION_FUNCTION_REFERENCE

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).

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.

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

Release History

12 December 2006

New content:
  • Added information about user-defined types, aliases, and XML schema collection dependencies when these objects are declared as parameters in Transact-SQL modules.
  • Added the Examples section.

Did you find this helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

ADD
Show:
© 2014 Microsoft. All rights reserved.