This project has moved. For the latest updates, please go here.

Determine the Derived Column Expression for a column in a Destination

Coordinator
Jan 29, 2013 at 11:53 PM

Assuming that you know the ObjectKey for a Destination, and the RunKey, then the following query will return all the derived columns, and their expressions.

Just replace the RunKey and ObjectKey values at the end of this query.

 

WITH DerivedColumns as
(
Select RunKey, ObjectKey, SUBSTRING([ObjectAttrName], CHARINDEX('[', [ObjectAttrName]) + 1, CHARINDEX(']', [ObjectAttrName]) - CHARINDEX('[', [ObjectAttrName]) - 1) AS ColumnName, SUBSTRING([ObjectAttrValue], CHARINDEX('Expression', [ObjectAttrValue]) + 11, 999) as Expression
FROM [dbo].[ObjectAttributes]
WHERE [ObjectAttrName] LIKE 'Derived Column Output%'
)
, TargetColumns as
(
Select RunKey, ObjectKey, SUBSTRING([ObjectAttrName], CHARINDEX('[', [ObjectAttrName]) + 1, CHARINDEX(']', [ObjectAttrName]) - CHARINDEX('[', [ObjectAttrName]) - 1) AS ColumnName
FROM [dbo].[ObjectAttributes]
WHERE CHARINDEX('[', [ObjectAttrName]) > 0
)
, ContainersChildren as
(
	SELECT DISTINCT [SrcObjectKey],TgtObjectKey, ObjectName, ISNULL(ObjectTypes.ObjectTypeName, ObjectTypeString) as ObjectTypeString 
	FROM [dbo].[ObjectDependencies]
			INNER JOIN [dbo].[Objects] 
				ON ObjectKey = TgtObjectKey
				AND [DependencyType] = 'Containment' 
	LEFT OUTER JOIN [dbo].[ObjectTypes] 
		ON ObjectTypes.ObjectTypeKey = ObjectTypeString 
)
, MyContainer as
( SELECT [SrcObjectKey], [TgtObjectKey]
	FROM [dbo].[ObjectDependencies]
	WHERE [DependencyType] = 'Containment'
)
SELECT DerivedColumns.ColumnName as SourceColumn, DerivedColumns.Expression
, ContainersChildren.ObjectName as SourceObject, ContainersChildren.ObjectTypeString as SourceType
, TargetColumns.[ColumnName] as TargetColumn, [Objects].ObjectName as TargetObject, [Objects].ObjectDesc as TargetType
FROM [dbo].[Objects]
INNER JOIN TargetColumns
	ON TargetColumns.ObjectKey = [Objects].[ObjectKey]
	AND TargetColumns.RunKey = [Objects].RunKey
INNER JOIN MyContainer
	ON TargetColumns.ObjectKey = MyContainer.TgtObjectKey
INNER JOIN ContainersChildren
	ON MyContainer.SrcObjectKey = ContainersChildren.SrcObjectKey
	AND ContainersChildren.ObjectTypeString = 'Derived Column'
INNER JOIN DerivedColumns
	ON TargetColumns.ColumnName = DerivedColumns.ColumnName
	AND ContainersChildren.TgtObjectKey = DerivedColumns.ObjectKey
WHERE TargetColumns.RunKey = 6
AND TargetColumns.ObjectKey = 8406
;