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)

SQL in the City - New York 2012

I was able to attend the SQL in the City event in New York this past Friday.  I highly recommend that anyone who can spare the time and travel should attend the next SQL in the City day in your area.  I found it to be a very well put-together event with useful content and excellent networking opportunities.

At the event I was able to meet both Steve Jones (who compassionately shook my hand as I walked in dripping wet from the rain during my walk over to 3rd Avenue) and Grant Fritchey (Scary DBA).  It was great to have even a brief conversation with each of them and introduce myself.  I also got to meet Product Manager David Atkinson and Developer David Simner and talk briefly about the awesome SQL Source Control and SQL Compare products.  The presentations I attended, "Database Maintenance Essentials", "Red Gate Tools - the Complete Lifecycle", and "The Whys and Hows of Database Continuous Integration" were all very informative and useful.

The highlight of the day was finding out about Red-Gate's Virtual Restore product.  This tool essentially allows you to mount a .BAK file as a database.  It supports multiple databases mounted from the same .BAK file, and it is not destructive to the .BAK file.  From the description, it works very similarly to VMs that are mounted from a disk file and then store changes in a disk "diff" file.  This tool allows you to save both space and time and sounds very awesome for developers.

I must say that I think Red-Gate has made a critical error with the marketing of this tool.  Developers hear "restore" and think "DBA tool"; in fact I think that's what the Red-Gate marketing team thought too as this application is included in their DBA bundle only.  I would absolutely use this tool as a developer and I might even use it as often as I use things like SQL Compare and the rest of the more developer-oriented tools.  It's a game-changer, and I hope Red-Gate figures out that they're doing a very bad job of marketing this product and could probably be making a lot more money from it by just getting the word out that it's not only a DBA tool.  For most of the other Red-Gate developer tools, a savvy developer could imagine cooking-up some sort of "good enough" workaround to not have to buy it (though certainly not in a shorter time than the reasonable expense for the license), but I would never even attempt creating something like Virtual Restore.  I will absolutely be trying out this software later this week.  Red-Gate - you've got to get the word out, even think about changing the name or something!!!

Thanks very much to everyone at Red-Gate for putting this event together.  I hope to see you again at the next SQL in the City.

Excellent SSIS 2012 Upgrade Video

This presentation gives an excellent overview of the process of upgrading SQL Server 2008 SSIS packages to work with SQL Server 2012 SSIS.  I’m very impressed at the much-improved capabilities for deploying SSIS packages in a less-scary way using package and project parameters, support for SSIS “environments” (“prod”, “dev”, “test”, etc.) and several other improvements including SQL Server 2012 automatically archiving previous versions of your packages when a new version is deployed.  It seems that it may actually be practical to use the built-in SSIS deployment functionality now.

http://channel9.msdn.com/Events/TechEd/NorthAmerica/2012/DBI329

This presentation was delivered by Sven Aelterman at TechEd 2012 – well done Sven!