DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE
SITE SEARCH SQLUSA.com HEADLINES NEWS
SQL E/BOOKS  SQL 2012 PROGRAMMING  DOWNLOADS
SCRIPTS SQL 2005 SQL 2008 ARTICLES
SQL JOBS TWITTER FORMAT VIDEOS
How to list object dependencies?

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

------------

 

Related MSDN article link:

sys.sql_expression_dependencies (Transact-SQL)

 

Exam Prep 70-461
Exam 70-461
DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE