Execute the following
Microsoft SQL Server T-SQL script in Query Editor to demonstrate how to list object dependencies.
-- SQL Server 2008 object dependency query - listing object dependencies
SELECT ReferencingObjectType = o1.type,
ReferencingObject = SCHEMA_NAME(o1.schema_id)+'.'+o1.name,
ReferencedObject = SCHEMA_NAME(o2.schema_id)+'.'+ed.referenced_entity_name,
ReferencedObjectType = o2.type
FROM AdventureWorks2008.sys.sql_expression_dependencies ed
INNER JOIN AdventureWorks2008.sys.objects o1
ON ed.referencing_id = o1.object_id
INNER JOIN AdventureWorks2008.sys.objects o2
ON ed.referenced_id = o2.object_id
WHERE o1.type in ('P','TR','V', 'TF')
ORDER BY ReferencingObjectType, ReferencingObject
-- Partial results
| Type |
ReferencingObject |
ReferencedObject |
Type |
| P |
dbo.uspGetBillOfMaterials |
Production.BillOfMaterials |
U |
| P |
dbo.uspGetBillOfMaterials |
Production.Product |
U |
| P |
dbo.uspGetEmployeeManagers |
HumanResources.Employee |
U |
| P |
dbo.uspGetEmployeeManagers |
Person.Person |
U |
| P |
dbo.uspGetManagerEmployees |
HumanResources.Employee |
U |
| P |
dbo.uspGetManagerEmployees |
Person.Person |
U |
| P |
dbo.uspGetWhereUsedProductID |
Production.BillOfMaterials |
U |
| P |
dbo.uspGetWhereUsedProductID |
Production.Product |
U |
| P |
dbo.uspLogError |
dbo.ErrorLog |
U |
| P |
dbo.uspLogError |
dbo.uspPrintError |
P |
------------
-- Find all stored procedures where a column is being used/referenced
USE AdventureWorks2008;
DECLARE @SchemaName sysname = N'Production';
DECLARE @TableName sysname = N'Product';
DECLARE @ColumnName sysname = N'ProductID';
SELECT QUOTENAME(refing.referencing_schema_name) +
N'.' + QUOTENAME(refing.referencing_entity_name) As SprocName
FROM sys.dm_sql_referencing_entities(QUOTENAME(ISNULL(@SchemaName,N'dbo')) +
N'.' + QUOTENAME(@TableName),'object') refing
CROSS APPLY sys.dm_sql_referenced_entities(QUOTENAME(refing.referencing_schema_name) +
N'.' + QUOTENAME(refing.referencing_entity_name), 'object') refed
WHERE EXISTS(SELECT * FROM sys.objects
WHERE refing.referencing_id = object_id and type ='P')
AND refed.referenced_schema_name = @SchemaName
AND refed.referenced_entity_name = @TableName
AND refed.referenced_minor_name = @ColumnName
ORDER BY SprocName;
/* SprocName
[dbo].[sprocGetWhereProductUsed]
[dbo].[uspGetBillOfMaterials]
[dbo].[uspGetWhereUsedProductID]
[dbo].[uspProductByColor] */
------------
Related MSDN article link:
sys.sql_expression_dependencies (Transact-SQL)
|
