3

I am looking for a way to query the 'MS_Description' extended property values of table columns that are returned in a view, not the extended property values on the view's columns itself.

I can get the view schema (How to list the source table name of columns in a VIEW (SQL Server 2005)) which returns view columns used in a view (although I only want columns that are returned).

And I can get extended properties: Select Extended Property from SQL Server tables

But I haven't worked out exactly how to marry the 2. But first, is there a built-in view or function that will automatically do this? I am using SQL 2014.

Or has anyone already written this query?

http://www.sqlfiddle.com/#!6/e3abc/1

Jay Cummins
  • 1,039
  • 2
  • 14
  • 31
  • You can't directly. Those extended properties belong to the base table. If you look at sys.extended_properties you will see the major_id is the same as the object_id of your table. The columns in your view are not the same column as in your base table. You would have to leverage dynamic sql to even have a chance at this. But why do you need to get the extended properties of a column used in a view in the first place? The only thing I could think of is trying to leverage that data for a label caption on an input form that is bound to a view. – Sean Lange Nov 27 '17 at 20:38
  • I'm using Enterprise Architect to generate a data dictionary, and I wanted to manually set the notes on the view w/ the table column description (software doesn't do it, so I'm trying to hack it). – Jay Cummins Nov 27 '17 at 21:29
  • It would be quite challenging to pull this off to say the least but a very interesting idea. – Sean Lange Nov 27 '17 at 21:48

0 Answers0