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

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 开发者_如何学Godo a reporting on it.

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

Can somebody help in this?

---

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.

---

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';

---

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.