Archive

Posts Tagged ‘sys.sql_expression_dependencies’

Using Catalog Views – sys.sql_expression_dependencies

13.06.2011 No comments

sys.sql_expression_dependencies catalog view contains a row for each dependent object which references another object in the database. For example, if a view uses a tables in it’s definition, then the view depends on the table.

For example, the view HumanResources.vEmployee in AdvendureWorks2008R2 database contains a reference to following tables:

FROM [HumanResources].[Employee] e

INNER JOIN [Person].[Person] p

INNER JOIN [Person].[BusinessEntityAddress] bea

INNER JOIN [Person].[Address] a

INNER JOIN [Person].[StateProvince] sp

INNER JOIN [Person].[CountryRegion] cr

LEFT OUTER JOIN [Person].[PersonPhone] pp

LEFT OUTER JOIN [Person].[PhoneNumberType] pnt

LEFT OUTER JOIN [Person].[EmailAddress] ea

 

These dependencies can be retrieved from sys.sql_expression_dependencies as below:

— © 2011 – Vishal (http://SqlAndMe.com)

 

USE AdventureWorks2008R2

 

SELECT      referenced_schema_name, referenced_entity_name,

            COL_NAME(referenced_id, referenced_minor_id)

FROM        sys.sql_expression_dependencies

WHERE       referencing_id = OBJECT_ID(N'HumanResources.vEmployee')

Result Set:

referenced_schema_name     referenced_entity_name           

———————-     ———————-     ———————

Person                     Address                    NULL

Person                     BusinessEntityAddress      NULL

Person                     CountryRegion              NULL

Person                     EmailAddress               NULL

HumanResources             Employee                   NULL

Person                     Person                     NULL

Person                     PersonPhone                NULL

Person                     PhoneNumberType            NULL

Person                     StateProvince              NULL

 

(9 row(s) affected)

You can find more information on sys.sql_expression_dependencies on BOL.

Hope This Helps!

Vishal