Object Dependency in SQL Server

A dependency is recorded in the database engine when a SQL Server object refers to another SQL Server object. Now, the question arises that what are a referenced entity and a referencing entity? An object, which is referred to by another object is called a referenced entity. An object that references another object in its definition and the system catalog stores that definition, then that object is called a referencing entity. For example in a view on a table, the table is the referenced entity and the view is a referencing entity.

Whenever a database is being developed, a relationship is established between those objects. A slight change made in the object might produce an effect on another object. The relationship between objects is made in order to share data and provide information. In order to avoid making any changes to the other objects while you don’t need a field in a table, check for object dependencies instead of deleting that field straightaway. An object dependency can be made to make sure that the field you want to delete is not used in another table.

Merits:

• Saves time
• It avoids mistakes
• More efficient way
• Tracking objects using the name of the object rather than object IDs
• Dependency can be tracked even if object is removed from the database
• Find any stored procedures that may not be in use or if they require any update

How to view dependency information?

1. 1.     Hit the database object where the dependencies are to be viewed in the Navigation pane.
2. 2.     Click on the Database Tools tab.
3. 3.     Click the Object Dependencies button.
4. 4.     Now Hit on OK to update dependency information and then click Yes to close all objects if urged.
5. 5.     Click the Objects that depend on me or Objects that I depend on option.(The Object Dependencies task pane shows all the list of objects that use the selected object).
6. 6.     To view dependency information Click the Expand icon next to an object and for hiding the dependency information Click the Collapse icon.

For keeping track of Object Dependencies, there are two new Dynamic Management Functions namely: sys.dm_sql_referenced_entities and sys.dm_sql_referencing_entities ,  a System View namely: sys.sql_expression_dependencies and Not available or Unresolved Entity.

1. 1.     sys.dm_sql_referenced_entities Dynamic Management Function: This dynamic management function provides all the entities in the current database referenced by a stored procedure or function and also returns one row for each user-defined object that is referenced by name within the definition of a specified referencing object.

Here, users should make a note that this script works in SQL Server version 2008 and above and a schema name should be also included while specifying the stored procedure name.

1. 2.     sys.dm_sql_referencing_entities Dynamic Management Function: All the entities in the current database that refer to the specified table are provided. One record for each user defined object is returned within the current database.

Here also, the schema name should be included, while specifying the table name. Without doing this, dependencies will not be display by the result.

sys.sql_expression_dependencies System or Catalog View: This investigation tool consists of one row for each by name dependency on a user-defined entity in the current database. There are basically two types of dependencies tracked by the database engine in SQL Server 2008 that are:

1. Schema-bound Dependency: This relationship between two objects prevents the referenced objects from dropping or getting modified till the existence of the referenced object.

2. Non-schema-bound Dependency: This relationship doesn't prevent the referenced object from dropping or getting modified.

Not available or Unresolved Entity: Under this script, the dependency type cannot be determined.

Using SQL Server Management Studio: This has an attractive feature namely Object Explorer that enables the user to browse, select, and act upon any of the objects within the server. The objects dependent on another object of interest can also be viewed and also those on which it depends on. SQL Server Management Studio also gives a better hierarchical view of the dependencies.

Author Bio: Priyanka Chouhan is a technical writer in Stellar Data Recovery  with 5 years of experience and has written several articles on SQL server & SharePoint. In the spear time she loves reading and gardening.

Read More on Database objects management

SQL Server - Open SSMS on the Command Line - SQL Server DBA

DROP SCHEMA script for multiple sql database users - SQL Server ...