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 .
The object which appears inside the SQL expression is known as referenced entity.
The object which has the SQL expression is known as a referencing entity.
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.
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');
431 total views, 3 views today