Monday 11 July 2011

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

No comments:

Post a Comment