How to find dependencies of object

There are several ways to find object dependencies in SQL Server

1. View Dependencies feature in SQL Server Management Studio 
2.  sp_depends system stored procedure - 

3. SQL Server dynamic management functions 

     sys.dm_sql_referencing_entities
     sys.dm_sql_referenced_entities

How to find dependencies using SQL Server Management Studio
Use View Dependencies option in SQL Server Management studio to find the object dependencies 

For example : To find the dependencies on the Employees table, right click on it and select View Dependencies from the context menu 

 

In the Object Dependencies window, depending on the radio button you select, you can find the objects that depend on Employees table and the objects on which Employeestable depends on. 

 

Identifying object dependencies is important especially when you intend to modify or delete an object upon which other objects depend. Otherwise you may risk breaking the functionality. 

For example, there are 2 stored procedures (sp_GetEmployees and sp_GetEmployeesandDepartments) that depend on the Employees table. If we are not aware of these dependencies and if we delete the Employees table, both stored procedures will fail with the following error.

Msg 208, Level 16, State 1, Procedure sp_GetEmployees, Line 4
Invalid object name 'Employees'.

There are other ways for finding object dependencies in SQL Server which we will discuss in our upcoming videos. 



sp_depends
A system stored procedure that returns object dependencies
For example,

• If you specify a table name as the argument, then the views and procedures that depend on the specified table are displayed
• If you specify a view or a procedure name as the argument, then the tables and views on which the specified view or procedure depends are displayed.

Syntax :Execute sp_depends 'ObjectName'

The following SQL Script creates a table and a stored procedure

Create table Employees

(

    Id int primary key identity,

    Name nvarchar(50),

    Gender nvarchar(10)

)

Go

Create procedure sp_GetEmployees

as

Begin

    Select * from Employees

End

Go

Returns the stored procedure that depends on table Employees

sp_depends 'Employees'

Ouptut :


Returns the name of the table and the respective column names on which the stored procedure sp_GetEmployees depends

sp_depends 'sp_GetEmployees'

Output : 

  
Sometime sp_depends does not report dependencies correctly. For example, at the moment we have Employees table and a stored procedure sp_GetEmployees. 

Now drop the table Employees

Drop table Employees

and then recreate the table again

Create table Employees

(

    Id int primary key identity,

    Name nvarchar(50),

    Gender nvarchar(10)

)

Go

Now execute the following, to find the objects that depend on Employees table

sp_depends 'Employees'

We know that stored procedure sp_GetEmployees still depends on Employees table. But sp_depends does not report this dependency, as the Employees table is dropped and recreated.
Object does not reference any object, and no objects reference it.

sp_depends is on the deprecation path. This might be removed from the future versions of SQL server.




How to find object dependencies using the following dynamic management functions

• sys.dm_sql_referencing_entities
• sys.dm_sql_referenced_entities
• Difference between 
• Referencing entity and Referenced entity
• Schema-bound dependency and Non-schema-bound dependency

The following example returns all the objects that depend on Employees table. 
Select * from sys.dm_sql_referencing_entities('dbo.Employees','Object')

Difference between referencing entity and referenced entity
A dependency is created between two objects when one object appears by name inside a SQL statement stored in another object. The object which is appearing inside the SQL expression is known as referenced entity and the object which has the SQL expression is known as a referencing entity.

To get the REFERENCING ENTITIES use SYS.DM_SQL_REFERENCING_ENTITIES dynamic management function

To get the REFERENCED ENTITIES use SYS.DM_SQL_REFERENCED_ENTITIES dynamic management function

Now, let us say we have a stored procedure and we want to find the all objects that this stored procedure depends on. This can be very achieved using another dynamic management function, sys.dm_sql_referenced_entities.  

The following query returns all the referenced entities of the stored procedure sp_GetEmployeesandDepartments

Select * from

sys.dm_sql_referenced_entities('dbo.sp_GetEmployeesandDepartments','Object')

Please note : For both these dynamic management functions to work we need to specify the schema name as well. Without the schema name you may not get any results.

Difference between Schema-bound dependency and Non-schema-bound dependency
Schema-bound dependency : Schema-bound dependency prevents referenced objects from being dropped or modified as long as the referencing object exists

Example : A view created with SCHEMABINDING, or a table created with foreign key constraint.

Non-schema-bound dependency : A non-schema-bound dependency doesn't prevent the referenced object from being dropped or modified.