sys.dm_sql_referencing_entities not returning results

by Kevin3NF   Last Updated January 12, 2018 01:06 AM

No doubt this is syntax or typo...but when I query sys.dm_sql_referencing_entities for a parent table, I get zero results. No error, just no results.

USE [source];
FROM sys.dm_sql_referencing_entities ('[Test].[USERS]', 'OBJECT');

When I view the dependencies from SSMS, I get

SSMS dependencies

I've never used this before, so maybe I am misunderstanding how this works or what it should be returning? I tried against the SUBSCRIPTIONS table as well, same result...nothing.

Answers 2

According to BOL:

A table is tracked as a referencing entity only when it references a Transact-SQL module, user-defined type, or XML schema collection in the definition of a computed column, CHECK constraint, or DEFAULT constraint.

Foreign key relationships would not show in this DMV.

Mark Sinkinson
Mark Sinkinson
April 26, 2016 14:52 PM

Referenced/referencing entity functions return instances where an object, column etc. is referenced directly (usually in other objects like stored procedures, but Mark has the full list from the documentation).

These functions are not meant to analyze foreign key dependencies, which you can get from the foreign key catalog views. This simple query assumes all tables are in the dbo schema, but you would need to add joins to sys.schemas in order to flesh those out otherwise:

SELECT 'FK points from ',, ' to ',
  FROM sys.foreign_keys AS fk
  INNER JOIN sys.objects AS f 
    ON fk.parent_object_id = f.[object_id]
  INNER JOIN sys.objects AS t 
    ON fk.referenced_object_id = t.[object_id];

The query in your question will return results if you create an object that references the table, e.g.:

CREATE VIEW [Test].MyView_DeleteMe
  SELECT * FROM [Test].Users;
Aaron Bertrand
Aaron Bertrand
April 26, 2016 14:54 PM

Related Questions

SQL Server purchasing options in AWS

Updated June 05, 2017 13:06 PM

Visual Studio SSIS Package throws error

Updated September 18, 2017 13:06 PM

SQL Server Performance Issues & Reporting Requirements

Updated October 18, 2017 11:06 AM

SSISDB Catalog in sql server

Updated June 03, 2016 08:02 AM