Finding Object Dependencies In SQL Server

In a sprawling database application, it can sometime be a nightmare if you need to alter the definition of a table, for fear that you will break an object that references it, that you were unaware of. (Although this will all be detailed in your documentation of course! ;-) )

But recently I found a great DMF, which is documented on MSDN, but I had never come across, that given the name of a table, will list all of the Procedures, Functions, Views and Triggers that reference it, and you can join it back to other system object, such as sys.sql_modules to pull back other useful information, such as is the object schema_bound (which will potentially stop you altering the table, and throw an error when you run the ALTER script.)

The DMF is called sys. dm_sql_referencing_entities and the example below will list the details of all objects that depend on a table called mySchema.myTable, along with if the object is schema bound, and even the object’s definition (providing it is not encrypted).

SELECT referencing_schema_name, referencing_entity_name, is_schema_bound, [definition]

FROM sys.dm_sql_referencing_entities ('mySchema.myTable', 'OBJECT') r

INNER JOIN sys.sql_modules m

                ON r.referencing_id = m.object_id

Find my book, Pro SQL Server Administration on Amazon -

America

United Kingdom