The sys.dm_sql_referencing_entities Dynamic Management Function is very similar to the function, sys.dm_sql_referenced_entities, that we reviewed yesterday. The difference is the function from yesterday allowed you to see all objects that are being REFERENCED as part of the object you specify. Today’s function takes this functionality in reverse and displays all objects that are REFERENCING the object that you specify. To help illustrate how this function works lets look at the function against the Person.Person table in the AdventureWorks2012 database.
SELECT * FROM sys.dm_sql_referencing_entities('person.person', 'OBJECT')
The results from this function display all of the database objects that are referencing the Person.Person table that we specified as one of the function arguments. If we look at row 2 of the result set, we will see the dbo.uspGetEmployeeManagers stored procedure which was used in yesterday’s blog about the sys.dm_sql_referenced_entities function.
If you are ever tasked with dropping database objects and you need to identify all objects within the same database that may be referencing your particular objects of interest, this is a good place to start that analysis.
For more information about this Dynamic Management Function, please see the below Books Online link:
Follow me on Twitter @PrimeTimeDBA