December 1, 2014 at 4:10 am
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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy