Using sys.sql_Expression_dependencies

• Jason-299789

  SSC-Insane

  Points: 21601

  More actions

  December 1, 2014 at 4:10 am

  #292407

  I am looking to use the sys.sql_experession_depenancies to build the base for a mapping document in an ETL Process.

  This is helped by having a contract layer consisting of views between the various load processes.

  I was looking at using something along the following lines

  SELECT

  QUOTENAME(Object_Schema_Name(sql_expression_dependencies.referencing_id))

  +'.'+QUOTENAME(Object_Name(sql_expression_dependencies.referencing_id))AS ReferencingObject

  , REPLACE(referencingobject.type_desc,'_',' ')AS ReferencingObjectType

  , QUOTENAME(sql_expression_dependencies.referenced_Schema_Name)

  +'.'+QUOTENAME(sql_expression_dependencies.referenced_entity_name)AS ReferencedObject

  , QUOTENAME(columns.name)AS ReferencedColumn

  , REPLACE(referencedobject.type_desc,'_',' ')AS ReferencedObjectType

  --, sys.sql_expression_dependencies.*

  FROM

  sys.sql_expression_dependencies

  JOIN sys.columns

  ON columns.object_id=referenced_id

  AND columns.column_id=referenced_minor_id

  JOIN sys.objects referencingobject

  ON referencingobject.object_id=sql_expression_dependencies.referencing_id

  JOIN sys.objects referencedobject

  ON referencedobject.object_id=sql_expression_dependencies.referenced_Id

  ORDER BY

  ReferencingObject

  , ReferencedObject

  , ReferencedColumn

  What I'd like to do is to also get the views aliased name as well, a Typical view is defined as

  CREATE VIEW MyView

  WITH SCHEMABINDING

  AS

  SELECT

  table1.col1AS FirstCol

  ,table2.col2AS SecondCol

  FROM

  table1

  JOIN table2

  ON table1.Id=table2.t1Id

  Looking at the base table this doesn't seem to be mapped, is this because it isn't available in the system table, or am I missing something?

  _________________________________________________________________________
  SSC Guide to Posting and Best Practices