I'm trying to put a data dictionary together for a client using SQL Server MDS. In order to avoid tracking the specific location in the data warehouse/marts/source systems of the fields being defined, I'm hoping to use the system views as much as possible.
This works fine for tracking the fields themselves. However, the users' reports work off cubes built on views in the data marts, and I'm having trouble tracking the source of calculated columns.
Taking this view as an example:
CREATE VIEW [dbo].[vw_testing_colltable]
AS
SELECT colid, coldesc, firstadd + secondadd AS totaladd
FROM dbo.testing_coltable
Where in the system views can I determine that firstadd and secondadd are part of the view and they are used to create totaladd?
INFORMATION_SCHEMA.VIEW_COLUMN_USAGE lists firstadd and secondadd but not totaladd.
INFORMATION_SCHEMA.COLUMNS lists totaladd but not firstadd and secondadd.
sys.columns combines the two.