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

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

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

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.

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

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

Azure SQL DB doesn't support extended properties yet.

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

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 :)

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

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

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

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

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

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!

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.

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

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.