Query Object Dependencies

SQL Server Management Studio has a decent interface for viewing object dependencies, check out TechNet for those procedures. But, we all know the value of being able to run queries for the information that we need. There are two methods that we’re going to cover for finding object dependencies. SQL Server 2005 is still in extended support, so first we will cover the deprecated method, sp_depends. Then we’ll discuss sys.dm_sql_referencing_entities and sys.dm_sql_referenced_entities which are the now preferred functions to use and available in SQL Server 2008 and above.

sp_depends

sp_depends has fairly straight forward syntax. Simply pass in the object name when connected under the context of the database that you are working in to get a list of the dependencies.

USE AdventureWorks2012;
exec sp_depends 'Person.Person';

sp_depends-results

Dynamic Management Functions

When using our DMFs we will first need to understand the terminology in their names. A referenced entity is an entity which at least one other entity depends upon. For example, if you have a user-defined function and a stored procedure which uses the UDF, the UDF is the referenced entity. In that case, the stored procedure would be the referencing entity. Referencing entities depend upon the existence of the referenced entity.

sys.dm_sql_referencing_entities is the DMF whose result set closely matches that of sp_depends. To use this DMF you will need to call it in a SELECT statement passing in the referenced entity name and its type.

USE AdventureWorks2012;
GO
SELECT referencing_schema_name, referencing_entity_name, referencing_id, referencing_class_desc, is_caller_dependent
FROM sys.dm_sql_referencing_entities ('Production.Product', 'OBJECT');
GO

dm_sql_referencing_entities-results

Similar to the referencing entities, the sys.dm_sql_referenced_entities DMF accepts an entity and object type. The only difference is that this entity’s input parameter accepts the referencing object and not the referenced.

USE AdventureWorks2012;
GO
SELECT referenced_schema_name, referenced_entity_name, referenced_minor_name,
referenced_minor_id, referenced_class_desc
FROM sys.dm_sql_referenced_entities ('ddlDatabaseTriggerLog', 'DATABASE_DDL_TRIGGER');
GO

This article has 1 comment

  1. […] 4 is available! DATABASE DELIVERY PATTERNS & PRACTICES – UPDATED ALM For The DBA Curah Query Object Dependencies Hey DBAs, Don’t Be Like the Secret Service! C-R-U-D The Basics Continuing the DSC Resource Kit […]

Leave a Reply

%d bloggers like this: