Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

New York City .NET

The author of the NYCdotNet blog is a VB.NET and SQL server developer living and working in New York City. The author seeks to promote clean, readable, efficient code in both VB and SQL from design to development, deployment, and application maintenance.

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)

Comments

Leave a comment on the original post [nycdotnet.blogspot.com, opens in a new window]

Loading comments...