Determine Object Dependencies in SQL Server

Introduction

When a schema definition of an object is changed, it may affect another database object, so it is essential to determine object dependency. For example I have a table and due to some business requirements, I need to drop some of the columns of this table but the column(s) may be used by the other database objects like a Stored Procedure, trigger, view and so on.

A database engine automatically tracks object dependency information and this information is maintained in the SQL Server system catalog. The dependency is created between two objects when one object is used in the definition of another object.

There are many way to determine object dependencies information. Some of the methods are describe below.

Method 1: Using SSMS (SQL Server Management Studio)

Right-click on the object for which you want to find the dependency and select the "View Dependencies" option. This method helps us to get the data via the GUI.

This will open the Object Dependencies browser, from this view we can determine which are the objects dependent on the selected object.

In the preceding example, I want to determine the dependencies of the table "EmployeeMaster". .From this method we can determine whether this table is used by some of the Stored Procedures and triggers.

table

From this view we can also determine which objects the table "EmployeeMaster" depends on.

Method 2 Using SP_DEPENDS and SP_MSDEPENDENCIES

SP_DEPENDS helps us to show the information about the database object dependencies. Using this Stored Procedure we cannot view the dependency outside of the current database.

EXEC sp_depends 'dbo.EmployeeMaster'

Using the SP_MSDEPENDENCIES system Stored Procedure, we can list which objects the selected object is dependent on.

EXEC sp_MSdependencies 'dbo.EmployeeMaster'

Method 3: Using System Table “syscomments”

The system table “sys.syscomments” contains a definition for each view, trigger, Stored Procedure, rule, default, CHECK and DEFAULT constraint in the "TEXT" column. From this table we can determine which objects the source object is used in.

SELECT DISTINCT so.name , so.type
FROM syscomments sc
INNER JOIN sysobjects so ON sc.id = so.id
WHERE sc.text LIKE '%EmployeeMaster%'

Output

Method 4: Using system View SQL_EXPRESSION_DEPENDENCIES

The system view “sys.sql_expression_dependencies” contains one row for each dependency by name of the user defined object in the current database. This view contains a column called referencing_id (it is the object id of the referencing entity) and referenced_id (it is the object id of the referenced object) that can be used to determine object dependency.

SELECT OBJECT_NAME(referencing_id) FROM sys.sql_expression_dependencies
WHERE referenced_entity_name = 'EmployeeMaster'

Output

Method 5: Using the dynamic management function “sys.dm_sql_referencing_entities”

This table value function internally uses the system table sys.sql_expression_dependencies to determine dependency.

SELECT * FROM sys.dm_sql_referencing_entities ('dbo.EmployeeMaster', 'OBJECT');

Output

Method 6: Using system View “INFORMATION_SCHEMA.ROUTINES”

This view is created based on the system tables: sysobjects and syscolumns. This view contains a Stored Procedure and a function definition in the current database. The limitation of this method is that we can only determine the dependency on a Stored Procedure and the function for the given object.

SELECT routine_name AS Name, routine_type AS Type FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_DEFINITION LIKE '%EmployeeMaster%'

Output

Conclusion

Using the preceding described methods, we can determine object dependencies in SQL Server 2008 and above versions.