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:

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

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:

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:


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: