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]; GO SELECT * FROM sys.dm_sql_referencing_entities ('[Test].[USERS]', 'OBJECT'); GO
When I view the dependencies from SSMS, I get
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.
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.
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 ', f.name, ' to ', t.name 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 AS SELECT * FROM [Test].Users;