SQL Server metadata: How to get description for columns in views

Question

SQL Server allows storing metadata like description for a table and its columns. The description for the columns can be retrieved from sys.extended_properties. There is also a way to set the description for a view (using the properties window). But there is no way (or at least I am not aware of at this moment) to put the description of a column in a view.

For a particular development, we are keeping the descriptions in the database itself. This allows us to keep it in a single place and do a reporting on it.

We wished to extend the same to include the views as well.

Can somebody help in this?

Mikael Eriksson · Accepted Answer · 2011-07-05 13:04:37Z

1

It works fine using SSMS 2008. Use the Object Explorer and select properties of the column in the view.

It does not work with SSMS 2005. You will get an exception Alter failed for Column 'ColumnName'. View columns cannot be modified or created. (Microsoft.SqlServer.Smo).

But you can use SSMS 2008 to add the meta data to a SQL Server 2005 database.

answered Jul 5, 2011 at 13:04

Mikael Eriksson

139k22 gold badges223 silver badges293 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Mikael Eriksson Over a year ago

@Kangkan – Using SSMS 2008 it works for both tables and views. With SSMS 2005 it works for tables but not with views.

Kangkan Over a year ago

Its great to knonw this. However the column MS_Description is to be added. Thanks a lot.

Kangkan Over a year ago

Another thought, can I pull in the description from the base tables for the views?

Mikael Eriksson Over a year ago

@Kangkan – Yes, you find them in sys.extended_properties.

Damien_The_Unbeliever · Accepted Answer · 2011-07-05 12:40:27Z

1

Certainly, SQL Server supports storing and retrieving metadata values against the columns of a view, but I don't think Management Studio supports anything like the properties window for it.

You can maintain the description, etc, by using sp_addextendedproperty and its related functions, e.g.:

EXEC sp_addextendedproperty 
@name = N'Description', 
@value = 'Postal Code lorem ipsum...',
@level0type = N'Schema', @level0name = 'dbo',
@level1type = N'View',  @level1name = 'MyView',
@level2type = N'Column', @level2name = 'PostCodeColumn';

edited Jul 5, 2011 at 12:40

answered Jul 5, 2011 at 12:34

Damien_The_Unbeliever

241k28 gold badges358 silver badges470 bronze badges

1 Comment

Kangkan Over a year ago

This way, it will be very hard for updating columns for all the views. What I am looking for is an interactive way of doing it.

DarthJDG · Accepted Answer · 2011-07-05 20:22:21Z

0

There can be a bypass solution. Create a Table for each view and from there it will be the same thing you were doing. Let's say, you have a view as VIEW_TEMP, then write:

SELECT * INTO TABLE_FOR_VIEW_TEMP FROM VIEW_TEMP

A table will be created and you can work with your documentation.

edited Jul 5, 2011 at 20:22

DarthJDG

16.6k11 gold badges52 silver badges56 bronze badges

answered Jul 5, 2011 at 11:30

Pankouri

6743 gold badges13 silver badges30 bronze badges

1 Comment

Kangkan Over a year ago

Documentation is not the final goal. The goal is to maintain the documentation and the structure side by side. For that matter, the documentation can be kept in a word doc also, but maintaining two disjoint is a pain and you are proposing something similar.

Collectives™ on Stack Overflow

SQL Server metadata: How to get description for columns in views

3 Answers 3

4 Comments

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related