Execute the following Microsoft SQL Server T-SQL script in Query Editor to demonstrate how to list object dependencies.
-- View column dependencies
SELECT ReferencingObject = SCHEMA_NAME(o1.schema_id) + '.' + o1.name,
ReferencedObject = SCHEMA_NAME(o2.schema_id) + '.'
+ ed.referenced_entity_name,
ColumnName = c.name,
ReferencedObjectType = o2.type,
ReferencingObjecType = o1.type
FROM AdventureWorks2012.sys.sql_expression_dependencies ed
INNER JOIN AdventureWorks2012.sys.objects o1
ON ed.referencing_id = o1.object_id
INNER JOIN AdventureWorks2012.sys.objects o2
ON ed.referenced_id = o2.object_id
INNER JOIN AdventureWorks2012.sys.sql_dependencies d
ON ed.referencing_id = d.object_id
AND d.referenced_major_id = ed.referenced_id
INNER JOIN sys.columns c
ON c.object_id = ed.referenced_id
AND d.referenced_minor_id = c.column_id
WHERE SCHEMA_NAME(o1.schema_id) + '.' + o1.name = 'HumanResources.vEmployee'
ORDER BY ReferencedObject,
c.column_id;
ReferencingObject
|
ReferencedObject
|
ColumnName
|
ReferencedObjectType
|
ReferencingObjecType
|
HumanResources.vEmployee
|
HumanResources.Employee
|
BusinessEntityID
|
U
|
V
|
HumanResources.vEmployee
|
HumanResources.Employee
|
JobTitle
|
U
|
V
|
HumanResources.vEmployee
|
Person.Address
|
AddressID
|
U
|
V
|
HumanResources.vEmployee
|
Person.Address
|
AddressLine1
|
U
|
V
|
HumanResources.vEmployee
|
Person.Address
|
AddressLine2
|
U
|
V
|
HumanResources.vEmployee
|
Person.Address
|
City
|
U
|
V
|
HumanResources.vEmployee
|
Person.Address
|
StateProvinceID
|
U
|
V
|
HumanResources.vEmployee
|
Person.Address
|
PostalCode
|
U
|
V
|
HumanResources.vEmployee
|
Person.BusinessEntityAddress
|
BusinessEntityID
|
U
|
V
|
HumanResources.vEmployee
|
Person.BusinessEntityAddress
|
AddressID
|
U
|
V
|
HumanResources.vEmployee
|
Person.CountryRegion
|
CountryRegionCode
|
U
|
V
|
HumanResources.vEmployee
|
Person.CountryRegion
|
Name
|
U
|
V
|
HumanResources.vEmployee
|
Person.EmailAddress
|
BusinessEntityID
|
U
|
V
|
HumanResources.vEmployee
|
Person.EmailAddress
|
EmailAddress
|
U
|
V
|
HumanResources.vEmployee
|
Person.Person
|
BusinessEntityID
|
U
|
V
|
HumanResources.vEmployee
|
Person.Person
|
Title
|
U
|
V
|
HumanResources.vEmployee
|
Person.Person
|
FirstName
|
U
|
V
|
HumanResources.vEmployee
|
Person.Person
|
MiddleName
|
U
|
V
|
HumanResources.vEmployee
|
Person.Person
|
LastName
|
U
|
V
|
HumanResources.vEmployee
|
Person.Person
|
Suffix
|
U
|
V
|
HumanResources.vEmployee
|
Person.Person
|
EmailPromotion
|
U
|
V
|
HumanResources.vEmployee
|
Person.Person
|
AdditionalContactInfo
|
U
|
V
|
HumanResources.vEmployee
|
Person.PersonPhone
|
BusinessEntityID
|
U
|
V
|
HumanResources.vEmployee
|
Person.PersonPhone
|
PhoneNumber
|
U
|
V
|
HumanResources.vEmployee
|
Person.PersonPhone
|
PhoneNumberTypeID
|
U
|
V
|
HumanResources.vEmployee
|
Person.PhoneNumberType
|
PhoneNumberTypeID
|
U
|
V
|
HumanResources.vEmployee
|
Person.PhoneNumberType
|
Name
|
U
|
V
|
HumanResources.vEmployee
|
Person.StateProvince
|
StateProvinceID
|
U
|
V
|
HumanResources.vEmployee
|
Person.StateProvince
|
CountryRegionCode
|
U
|
V
|
HumanResources.vEmployee
|
Person.StateProvince
|
Name
|
U
|
V
|
-- 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] */
------------
No comments:
Post a Comment