sys.dm_sql_referencing_entities in SQL Server

Suggested Videos
Part 129 - Difference between DateTime and DateTime2 in SQL Server
Part 130 - Offset fetch next in SQL Server 2012
Part 131 - Identifying object dependencies in SQL Server



In this video we will discuss 

• 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

This is continuation to Part 131, in which we discussed how to find object dependencies using SQL Server Management Studio. Please watch Part 131 from SQL Server tutorial before proceeding.

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.