Query to Find "Update" Dependencies on a Table or View (SQL Server 2012)

The built-in dependency finder in SQL Server Management Studio does not provide the ability to distinguish between dependencies that are read-only and dependencies that are read-write.  However, SQL Server 2012 does make this information available via the management views.

 

The query below will return all of the objects that reference the table (or view) identified by @SchemaName and @ObjectName where the referencing object can modify the data in the table (or view)... specifically this means an INSERT, UPDATE, or DELETE operation.

 

Note that dependency tracking in SQL Server is not perfect because of the possibility of using dynamic SQL, but this will hopefully be useful information.

DECLARE @SchemaName NVARCHAR(128) = 'dbo';

DECLARE @ObjectName NVARCHAR(128) = 'MyTableName';

SELECT re.referencing_schema_name, re.referencing_entity_name,

  ref.*

FROM sys.dm_sql_referencing_entities(@SchemaName + '.' + @ObjectName,'OBJECT') re

CROSS APPLY sys.dm_sql_referenced_entities(referencing_schema_name + '.' +

    referencing_entity_name,'OBJECT') ref

WHERE ref.is_updated = 1

  AND ref.referenced_entity_name = @ObjectName

  AND (ref.referenced_schema_name IS NULL OR ref.referenced_schema_name = @SchemaName);

 

This post was edited to fix code formatting and to specify SQL Server 2012 as this sadly does not appear to be supported in SQL Server 2008 or before.

 

References

MSDN: sys.dm_sql_referenced_entities (Transact-SQL)

MSDN sys.dm_sql_referencing_entities (Transact-SQL)