1

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.

S3S
  • 24,809
  • 5
  • 26
  • 45
El Kabong
  • 23
  • 4
  • 1
    Well...those two columns would show up in usage because they are used. And of course they are not columns in the view, only totaladd is a column in the view. I am a little bit confused about what you are trying to determine. – Sean Lange May 24 '17 at 15:37
  • My dictionary in MDS will list totaladd as a field. I'd like to be able to tell that it's used in vw_testing_colltable and then be able to trace up the path through the data warehouse via the fields that are used to create it (e.g. dbo.testing_colltable). – El Kabong May 24 '17 at 15:47
  • Seems like you did that. If it is a column in a view but in the underlying tables it must be a derived column. – Sean Lange May 24 '17 at 16:04
  • Up to the view, yes. But the user would like to analyze the field beyond the view -- to be able to know totaladd is used in the view, first and secondadd are used in this table -- without having to leave the report. – El Kabong May 24 '17 at 16:19
  • huh??? Why are users concerned about what database columns are on a report? I understand what you are saying but the only way to find that information is in the ddl of your views. The amount of complexity involved would be staggering. Consider if the column in your view came from a function. And the function uses a remote table. How could you possibly trace that? The possibilities are endless here. It is not as simple as you are making it out to be. – Sean Lange May 24 '17 at 16:22
  • Was looking to see if there was a way to do it that I'd missed. Obviously I haven't missed it, it's just not possible, so we'll do what we can reasonably. thanks for the input. – El Kabong May 24 '17 at 20:27

0 Answers0