THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Jamie Thomson

This is the blog of Jamie Thomson, a data mangler in London working for Dunnhumby

Extended property support in SSDT (10 days of SSDT – Day 5)

SQL Server Data Tools (SSDT) was released in Spring 2012 and I have been using it to build SQL Server databases nearly every day since then. In that time I’ve learnt a lot about how to make best use of the tool and I want to share that experience in a new series of blog posts called “10 days of SSDT”. I shall be publishing a different blog post every day for 10 days, each day revealing something that you may not know about SSDT. I hope you enjoy the series and contribute via the comments!

Extended properties are a feature of SQL Server that have existed since SQL Server 2000 although I assume that you probably don’t use them as I rarely see them being used on my travels. Nonetheless extended properties are a useful way of supplying extra information about your database. Most often they are used for documentation purposes however I have also used them to drive workflow in an ETL process (e.g. I toggle the loading of a table by changing one of its extended properties). SQL Server Data Tools (SSDT) includes good support for extended properties and in this blog post I’ll cover what support it provides.

First is SSDT’s table designer. Notice in this screenshot that we have the option to choose what information is displayed per column and that “Description” is one of those chosen columns:

SNAGHTML705b570

If I enter a value into that Description for a column then check out what happens:

SNAGHTML714660f

SSDT creates the DDL that creates an extended property named MS_Description on the column in question. You can create your own extended properties as well of course but unfortunately only extended properties called MS_Description will get surfaced in the UI (and even then only for columns). If you wish to create your own then simply create them as per the code above, using GO to separate them if you have more than one defined in the same script.

One nice aspect to this support is that SSDT treats extended properties as schema rather than data hence if I change the name of the column like so:

image

then an error appears, along with a red squiggly, informing you of an invalid reference in the extended property definition. Note that refactoring is supported for extended properties so if you change the name of a referenced object using refactor rename then the extended property will get updated accordingly.

At publish time SSDT will take care of creating the extended property if it doesn’t exist or modifying it if it does, just like any other schema object. Here’s the output from publishing my project to LocalDB:

image

image

OK, that’s pretty much everything there is to know about extended property support in SSDT. Any questions? Let me know.

@Jamiet

If you want to learn more about SSDT then come along to my training course Introduction to SQL Server Data Tools in association with Technitrain on 23rd/24th February 2015 in London. There is a discount if you register before the end of 2014.

Earlier posts in this series:

Published Tuesday, September 23, 2014 8:44 AM by jamiet

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

suhail ali said:

Do custom extended properties show up on the table designer or is only the description property supported?

September 23, 2014 5:44 PM
 

jamiet said:

Hi Suhail,

Only the description property is supported on the GUI of the Table Designer.

Regards

Jamie

September 24, 2014 2:58 AM
 

Jamie Thomson said:

SQL Server Data Tools (SSDT) was released in Spring 2012 and I have been using it to build SQL Server

September 24, 2014 3:06 AM
 

Jack Corbett said:

Jamie,

You can set the MS_Description property for tables as well.  Open the table in table designer, open the properties window, and you'll see the description property is available.  This also creates the code to create the Description extended property.

September 24, 2014 3:51 PM
 

Jamie Thomson said:

SQL Server Data Tools (SSDT) was released in Spring 2012 and I have been using it to build SQL Server

September 25, 2014 3:36 AM
 

Greg Low said:

Does Azure SQL DB support extended properties yet? I recall that it used to barf on them.

September 25, 2014 4:40 AM
 

jamiet said:

Oh, good question Greg. I confess I don't actually know.

September 25, 2014 4:47 AM
 

Steven Wright said:

Azure SQL DB doesn't support extended properties yet.

September 25, 2014 5:19 AM
 

Jamie Thomson said:

SQL Server Data Tools (SSDT) was released in Spring 2012 and I have been using it to build SQL Server

September 26, 2014 2:40 AM
 

Mark Wojciechowicz said:

I don't really use extended properties anymore.  I used them for extensive documentation on a couple of projects, but the problem is that they are so hard to find or to realize that they are even there.  They are invisible unless you are hunting for them.  If I want to see the extended props on a table, I click on the properties of the table and go to the tab.  There is not visual cue that anything will even be there.  If I want to see the props on each column, then I have to close that table dialogue and open the properties for each and every column.  

Now about that syntax - granted, it is very generic and, therefore, can be applied anywhere in any way, but it is hardly intuitive.  I wish it could be more like sp_rename, in the very least.  So maintenance of multiple properties on each table or each column is absolutely painful - even in SSDT which will drop it all at the bottom of the table script.

To drive the ETL process? I would rather a table that smacks me in the face and says this is the ETL schedule table.  From a maintenance point of view, devs would have a learning curve and you would likely need a web app in prod to surface the settings so mere mortals could change them.  It's an interesting use - I'll give you that :)

September 26, 2014 9:38 AM
 

jamiet said:

Hi Mark,

Thanks for the comment. Valid points, I can't deny that.

What I do is provide a view that "pivots" the extended properties so that we get a row per object, with all its extended properties exposed as columns. makes the whole thing very queryable. I also provide a stored proc to modify them i.e.:

exec ModifyExProp @TableName, @ExPropName, @Value

Does that make the idea any more enticing?

The reason I like this approach is that I don't have to maintain a separate "metadata" table that lists all of the tables in the DW - each table inherently appears in the view and its easy to see which extended props have not been supplied.

JT

September 26, 2014 9:50 AM
 

Mark Wojciechowicz said:

That does improve the usability problem quite a bit.  I did something similar with SSRS reports for viewing the properties, but the actual maintenance of them in SSDT was tedious, especially if there were a number of them.  Maintaining a metadata table is much easier and new devs coming in will expect something like that.  I would be concerned about adoption.

I don't think I would run the ETL with it, still, but do I give you props for the innovation.  It's an interesting use for it that I never considered, which makes me wonder about other possibilities.

Thanks for sharing

m

September 26, 2014 10:38 AM
 

Jamie Thomson said:

SQL Server Data Tools (SSDT) was released in Spring 2012 and I have been using it to build SQL Server

October 1, 2014 2:37 AM
 

Jamie Thomson said:

SQL Server Data Tools (SSDT) was released in Spring 2012 and I have been using it to build SQL Server

October 13, 2014 2:57 AM
 

Martin Guth said:

Hi Mark,

Hi Jamie,

I am happily using Extended Properties in SQL Server. Apart from MS_Description I use custom Extended Properties Author, CreationDateTime, ChangeDateTime and ChangeHistory.

I would absolutely welcome better UI Support in MS Tools as you stated but I have found some nice workarounds.

For maintaining them I use Data Dictionary Creator (https://datadictionary.codeplex.com/) which makes it much easier maintaining per column properties. For display and documentation I use Schema Spy, a java application which generates a nice HTML documentation. I have just pimped the customizable SQL to get my custom properties displayed as well in an HTML table.

Last but not least, just like Jamie, I have written a TSQL procedure which sets default entries for extended properties except MS_Description and another one which synchronizes PropertyValues between Tables (very useful in a DWH environment where you have got nearly the same table in production, staging and the dwh itself).

@Jamie: Thanks for the blog posts. Quite interesting. Unfortuantely I tried SSDT for quite a while and then stopped using it because I couldn't get a fix for all that unresolved references errors while having cross database references in my imported projects.

Best regards

Martin

October 24, 2014 11:07 AM
 

Nick Spain said:

Hi Jamie,

Do you know if SSDT executes sp_updateextendedproperty to update the extended property description?  Or does SQL Server execute some kind of alter table statement?

For context I promote metadata definitions from my data model to the database as extended properties.  Erwin does this really nicely.

Thanks,

Nick

December 30, 2014 6:47 AM
 

Tony Scott said:

Hi Jamie,

Great blog, very interesting.

I have been a proponent of using Extended Properties for many years and have written many procedures to both access and maintain them, though the one issue I have always faced is the portability of such properties.

Using tfs as the release management medium doesn't bode well with Extended Properties from what I can understand, and so I am now looking at creating my own DD in an admin database on each instance, and then using SSIS to update across instances, Dev-QA-UAT-Prod. I appreciate that I could use the same for accessing and copying over Extended Properties, but it seems just as easy to have the information tabulated, which then allows for a common DD across databases on the same instance for standards checking and comparison.

Any advice on this approach as compared to simply using Extended Properties?

Thanks again for a great blog!

May 14, 2015 10:44 AM
 

Gary Melhaff said:

Jamie, such great stuff...I would add that all databases have metadata properties and this of course isn't SSDT specific in any way - just some of the way SSDT supports it. But what's truly unique with Sql Server is you can create custom metadata which is an incredible advantage I'm afraid too few practitioners do not utilize.

Someone mentioned that it seems hidden but I would strongly disagree - it's available via SSMS and query-able from the dictionary. In fact I have a very nice metadata report built in SSRS that provides full table/view/column definitions and source data lineage (and soon business/transformation rules) for my Data Warehouse just using the default and custom metadata properties! In fact our SSRS report dynamically picks up new properties as they are added so they automatically show up in the report.

No longer do I need a custom metadata solution.

May 22, 2015 2:01 PM
 

jorge said:

Why I don´t see the "Description" column in the visual designer?. I have the last version of SSDT and the "Description" column is missing the option with check-boxes to select what columns you want to see.

:-/

Thanks

January 30, 2016 6:12 AM
 

James said:

How to query the extended properties of a TSqlObject(Column) in T4?

I cannot find a way to access the extended properties to influence code generation.

Is there a better place to store metadata against a column?

Thanks.

June 21, 2016 7:45 AM

Leave a Comment

(required) 
(required) 
Submit

This Blog

Syndication

Privacy Statement