Tuesday, January 27, 2015

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
dbo.uspGetBillOfMaterials Production.BillOfMaterials
dbo.uspGetBillOfMaterials Production.Product
dbo.uspGetEmployeeManagers HumanResources.Employee
dbo.uspGetEmployeeManagers Person.Person
dbo.uspGetManagerEmployees HumanResources.Employee
dbo.uspGetManagerEmployees Person.Person
dbo.uspGetWhereUsedProductID Production.BillOfMaterials
dbo.uspGetWhereUsedProductID Production.Product
dbo.uspLogError dbo.ErrorLog
dbo.uspLogError dbo.uspPrintError
------------

-- 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]  */
------------

Source : sqlusa

No comments:

Post a Comment