Identifying Object Dependencies in SQL Server

In, SQL Server there are two Dynamic Management functions that is used to keep track of Object Dependencies sys.dm_sql_referenced_entities and sys.dm_sql_referencing_entities.

Object dependency is referred by two entities that is referencing and referenced .

Referenced entity

The object which appears inside the SQL expression is known as referenced entity.

Referencing entity

The object which has the SQL expression is known as a referencing entity.

 sys.dm_sql_referenced_entities

The sys.dm_sql_referenced_entities is the Dynamic Management Function that returns one row for each user defined object which is referenced by name within the definition of a specified referencing object.

sys.dm_sql_referencing_entities

The sys.dm_sql_referencing_entities is the Dynamic Management Function that returns one record for each user defined object within the current database which refers to another user defined object by name.

To demonstrate the scenario, we create one table and one stored procedure that will refer to this table.

CREATE TABLE dbo.SalesData 
(
SalesId INT Identity(1,1) ,
SalesAmount Numeric (9, 2) 
)
GO

CREATE PROC getSalesData

AS

BEGIN

SELECT SalesId, SalesAmount FROM dbo.SalesData

END

Following Statement uses dynamic management function sys.dm_sql_referencing_entities that returns all the objects which are referencing to “SalesData”  table.

SELECT 
referencing_schema_name , referencing_entity_name ,
referencing_class_desc 
FROM sys.dm_sql_referencing_entities ('dbo.SalesData', 'OBJECT');

Following Statement uses dynamic management function sys.dm_sql_referenced_entities that returns all the objects which are referenced to “getSalesData”  stored procedure.

SELECT 
referenced_schema_name , referenced_entity_name , 
referenced_minor_name 
FROM sys.dm_sql_referenced_entities ('dbo.getSalesData', 'OBJECT');