Tuesday, July 23, 2013

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)

2 comments:

  1. What version of SQL server does this script work on?
    I get the error: Invalid column name 'is_updated'.
    When I try running it.

    This is an AWESOME script that I would love to have in my toolbox.

    Chris
    http://chrisbarba.com

    ReplyDelete
    Replies
    1. Sadly, this appears to be SQL 2012 and higher only. I should have thought to look that up before posting. I will update the article title and text.

      Delete