New Dynamic Management Views in SQL Server 2008
I'm staying on the same path in regards to dynamic management views (DMVs from here on) but turning my attention to the SQL Server 2008 product. There are roughly 30 new DMVs in 2008 and I am going to highlight two today.
I seem to be constantly looking for referenced objects in T-SQL code and there are a multitude of paths to choose to get there. All of them have slight issues that leave me wondering if I truly found all the objects I was after. SQL Server 2008 introduces two dynamic management functions that make this search easier.
sys.dm_referenced_entities
This function takes a qualified name (schema + object) as well as a type and returns all objects that are used by the given object. The types that can be passed to the function are OBJECT, TYPE, XML_SCHEMA_COLLECTION, and PARTITION_FUNCTION. For today's code examples I'm using the freshly downloaded AdventureWorks sample database for SQL Server 2008. The following code will return all objects referenced in the stored procedure uspGetBillOfMaterials:
SELECT
referenced_schema_name
,referenced_entity_name
,referenced_minor_name
,referenced_class_desc
FROM sys.dm_sql_referenced_entities('dbo.uspGetBillOfMaterials', 'OBJECT')
WHERE referenced_minor_id > 0 --return columns only;
sys.dm_referencing_entities
This function works in the opposite direction of sys.dm_referenced_entities and returns the objects dependent on the given object. Again, we pass a qualified name and a type. The following code will return all objects dependent on the table Production.BillOfMaterials:
SELECT
referencing_schema_name
,referencing_entity_name
,referencing_class_desc
FROM sys.dm_sql_referencing_entities('Production.BillOfMaterials', 'OBJECT');
I think these functions will make your next search for a table or procedure reference much easier.
Enjoy!