Using sys.sql_Expression_dependencies

  • 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

Viewing post 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply