SQLServerCentral / Article Discussions / Article Discussions by Author / Discuss content posted by Adam Aspin / Extended Properties Introduction / Latest Posts

RE: Extended Properties Introduction

Yitzchok Lavi — Tue, 04 Sep 2012 10:16:02 GMT

Has anyone found documentation of the Extended Properties available in SQL Server 2012 to parallel that for 2005-2008R2 which is available at [url]http://msdn.microsoft.com/en-us/library/ms190243.aspx[/url]??

RE: Extended Properties Introduction

Cade Roux — Tue, 22 Mar 2011 14:16:29 GMT

[quote][b]aigl (3/21/2011)[/b]Is it possible for View columns to inherit the Extended Properties from the Table columns that the View is based on?[/quote]I don't think so, and I wasn't even able to apply extended properties to columns of views (although I was able to apply them to views themselves)However, I see in this latest article, Adam has a query which is supposed to expose them, so I'm going to go revisit that.

RE: Extended Properties Introduction

Adam Aspin — Tue, 22 Mar 2011 02:20:52 GMT

Hi Aigl,I have never used extended properties in reporting services, so please share the knowledge if you get this working.Also, as far as I know, you have to specify column view properties per view (or write the code to extract and attribute them) and there is no simple inheritance.Regards,Adam

RE: Extended Properties Introduction

Adam Aspin — Tue, 22 Mar 2011 02:18:23 GMT

Hi Danjam,You are, of course, quite right about indexed views and standard version - thanks for correcting this.Adam

RE: Extended Properties Introduction

aigl — Mon, 21 Mar 2011 22:35:42 GMT

I see great use of the Extended Properties if you can make use of them as tool tips in Reporting Services. Hopefully one of the future topics will cover that.Is it possible for View columns to inherit the Extended Properties from the Table columns that the View is based on?

RE: Extended Properties Introduction

JediSQL — Sat, 19 Mar 2011 11:14:30 GMT

This regards indexed views. Indexed views are available in standard edition. What is limited to the enterprise/developer edition with regards to indexed views is the query optimizer's use of indexed views to enhance performance of queries that do not explicitly reference an indexed view, but there is an indexed view that has some of the joins already computed.

RE: Extended Properties Introduction

JediSQL — Sat, 19 Mar 2011 11:06:26 GMT

This regards the use of the MS_ prefix. SQL Server Management Studio and the third party "SQL Documentation" tools I have demoed use the MS_Description as the name/property that they display. So I have set up our extended property system to use MS_Description for our general description comments. That way, someone using SQL Server Management Studio or one of the third party tools will get our main description comment. But it is good to know that Microsoft uses other MS_* names/properties and, in general, the MS_ prefix should be avoided.I went with Microsoft's lead on using MS_ as a prefix to set up a name space, so I have our team using our own prefix, CSIT_ ([url=http://www.CureSearch.org]CureSearch[/url] Information Technology).In our industry, cancer research, the NCI (National Cancer Institute) is essentially requiring all of its grantees to use its [url=http://en.wikipedia.org/wiki/ISO_11179]ISO-11179[/url] meta data repository. Once you get used to it, it's pretty cool. We are setting it up so that all our columns, etc., will be assigned data element descriptions, and the assigning of data elements to columns will automatically update the MS_Description property for casual inspection.

RE: Extended Properties Introduction

Patrick2525 — Fri, 18 Mar 2011 08:35:39 GMT

[quote][b]ACinKC (3/17/2011)[/b][hr]I created an Excel/VBA solution for my current gig that read in the metadata for the tables and columns into a workbook (one worksheet per table) [...] It's a two-way tool, creating the Data Dictionary (in Excel) from the database and using the same tool in turn to update the EP values in the database.[/quote]Sounds interesting... is that something you can share? :-)

RE: Extended Properties Introduction

Jeff Moden — Thu, 17 Mar 2011 22:08:38 GMT

[quote][b]Adam Aspin (3/17/2011)[/b][hr]Thanks Jeff - praise from a leading light like yourself makes my day!There are three more articles to come (one a week for 3 weeks) which, hopefully, will help newbies not only add, but also extract and update this metadata in a comprehensive manner.Adam[/quote]I saw that in your intro and I'm very much looking forward to them.And, thank you for the very kind words. :blush:

RE: Extended Properties Introduction

Vedran Kesegic — Thu, 17 Mar 2011 17:24:38 GMT

I heard (from a person closest to the author) that in one of the next releases of XDetails plugin for Sql Management Studio it will be possible to easily edit column comments. It will be editing in-line in grid that displays descriptions near the table columns' names and types.

RE: Extended Properties Introduction

Charles Kincaid — Thu, 17 Mar 2011 16:18:25 GMT

Another trick is to set up a Generic/Text Only printer on your PC and redirect that to a file.

RE: Extended Properties Introduction

James Goodwin — Thu, 17 Mar 2011 14:47:44 GMT

YSLGuru,[quote]ANyone know of a good way to get Meta data (sometimes called a data dictionary) out of a PDF and into something more easy to insert into t-SQL code? The PDF currently uses an Excel/Spreadhseet/table like structre; at least thats they way its presented. I have no diea how PDF works internally and so its looking like its in a table may not mean it any easier to export then if it were presented in free form style.[/quote]Internally, PDF is a compressed PostScript file (with proprietary add ons), and as far as I can tell there isn't any good way to extract the data out.However, In theory, one could print the pdf to a PostScript file (e.g. set up a postscript printer and change the destination to be a file) and then use the postscript language to extract the data out. There may be converters to turn the postscript into something more friendly but I've never had occasion to look for any. It might even be possible to skip the postscript and set up some sort of line printer to create the file and parse that out after stripping out the control characters. If you decide to try this use the oldest printer driver you can make work (The Apple Laserwriter has historically been a good choice for a postscript printer driver). Tables might not be too bad but the more formatting and objects that exist in the pdf the harder it will be to parse out the file.After writing all of that, I thought of a potentially much easier way:OCR.Good Luck,

RE: Extended Properties Introduction

Adam Aspin — Thu, 17 Mar 2011 13:31:49 GMT

Charles - have you looked at http://datadictionary.codeplex.com/ - free, and very good!

RE: Extended Properties Introduction

Cade Roux — Thu, 17 Mar 2011 13:27:35 GMT

[quote][b]Alexander Kuznetsov (3/17/2011)[/b][hr]Hi Adam,The following sounds like a good reason: "tracks object evolution automatically".Thanks for the answer.AK[/quote]The only problem (re: object evolution) I've found is in SQL Server 2005 on user-defined functions, the ALTER FUNCTION statement would cause extended properties to be lost. I think this was fixed in 2008. This is not a problem with ALTER PROCEDURE.Renaming objects or changing schemas seemed to work fine in SQL Server 2005.

RE: Extended Properties Introduction

Adam Aspin — Thu, 17 Mar 2011 13:25:59 GMT

Hi Charles,Thanks for the feedback. As to a tool to manage documentation better - I would prefer to see Microsoft extend the product to include this.Regards,Adam

RE: Extended Properties Introduction

SQLRNNR — Thu, 17 Mar 2011 12:53:27 GMT

Nice article. Thanks.

RE: Extended Properties Introduction

Charles Kincaid — Thu, 17 Mar 2011 12:40:05 GMT

So here is a couple of questions.Let's say that there is a tool that lets you:Log into a SQL server.Update the description extended properties on your tables and columns.Lets you export the same to an XML file.Lets you import the same from an XML file.Would such a tool be worth say $5 US? What else must it do to be worth that?

RE: Extended Properties Introduction

Charles Kincaid — Thu, 17 Mar 2011 12:34:50 GMT

Great article Adam.

RE: Extended Properties Introduction

Adam Aspin — Thu, 17 Mar 2011 12:24:01 GMT

Thanks for the encouragement, Sal!

RE: Extended Properties Introduction

Adam Aspin — Thu, 17 Mar 2011 12:22:33 GMT

Hi YSLGuru,No idea on this one, I am afraid - sorry!Adam

RE: Extended Properties Introduction

stevemc — Thu, 17 Mar 2011 11:26:27 GMT

Enjoyed the article right up to the point where it linked to the SQL Server documentation on the feature, and felt compelled to look. Don't bother. Looks like documentation I would have written.

RE: Extended Properties Introduction

Sal Young — Thu, 17 Mar 2011 11:09:30 GMT

[font="Verdana"]Adam,I read your article and enjoyed every bit of it. Your writing style is superb and is consistent with the word “Introduction” from the title. I have read many articles about extended properties to end up with more questions after reading them, but your article is the best I have read at this level. You clearly described the basics of MS SQL Server extended properties and their possible use.Now I have to read all your other articles and will be waiting for the next two on the subject of Extended Properties.[/font]

RE: Extended Properties Introduction

Alexander Kuznetsov — Thu, 17 Mar 2011 10:54:07 GMT

Hi Adam,The following sounds like a good reason: "tracks object evolution automatically".Thanks for the answer.AK

RE: Extended Properties Introduction

YSLGuru — Thu, 17 Mar 2011 10:42:55 GMT

Great piece!Meta Data / Extended Property question:I have a PDF file (Man I hate PDF) that has thousands of details on Tables and columns for a very large DB (schema wise, there are over 2,000 tables) and I woudl desperately like to find a way to get that info form the PDF into SQL Server Extended properties.ANyone know of a good way to get Meta data (sometimes called a data dictionary) out of a PDF and into something more easy to insert into t-SQL code? The PDF currently uses an Excel/Spreadhseet/table like structre; at least thats they way its presented. I have no diea how PDF works internally and so its looking like its in a table may not mean it any easier to export then if it were presented in free form style.

RE: Extended Properties Introduction

Cade Roux — Thu, 17 Mar 2011 10:02:52 GMT

I have a presentation I've given a couple times related to metadata and extended properties:http://code.google.com/p/caderoux/wiki/LeversAndTurtlesUsing it to fine tune code generation so that code can be re-generated without re-applying manual tweaks is very powerful. You can also use it to manage system self-tests.I have applied to give this talk again at SQL Saturday in Pensacola and Baton Rouge.Adding some layers of utility functions and views can help to simplify the awkward "API" which SQL Server provides. I liked this article, because it gave a good indication of the problems with "levels". In my shell API, I wrapped the levels with dotted identifiers (schema.table.column) to make things more uniform.

RE: Extended Properties Introduction

Adam Aspin — Thu, 17 Mar 2011 09:32:00 GMT

Hi Bill,All these years & I never thought of using metadata to hold GUI attributes - thanks for the idea!Adam

RE: Extended Properties Introduction

Leonel Umaña Araya — Thu, 17 Mar 2011 09:12:31 GMT

Thanks, excellent article. I'll be waiting for the next ones. :-D

RE: Extended Properties Introduction

Bill Talada — Thu, 17 Mar 2011 09:07:46 GMT

I tried to make extended properties work for a year but found them too much to manage with all the change scripts I generate day-to-day. Eventually I reworked them as a few tables as a data dictionary which was much simpler.Storing descriptions seems the least useful reason for extended properties. Attaching attributes to tables such as "inserts, never updates" or "sync up and down", attributes used by development and deployment tools seems much more useful because they can be integrated as code.I'd also see advantages to storing "edit masks" and "display formats" and other GUI attributes as column extended properties. Then, using those in code generation solutions.

RE: Extended Properties Introduction

Adam Aspin — Thu, 17 Mar 2011 09:06:33 GMT

Hi Alexander,Using your own structure is infinitely more adaptable, and can be tailored to the specific requirements of your documentation in my experience.However, the major downside is that keeping object changes synchronised between a customised documentation structure and the built-in metadata can be hard work - and /or require lots of clever SQL as well as manual labour that you have to run to ensure that everything is in synch.So using the MS supplied metadata for documentation is clunky - but it tracks object evolution automatically, which is a major advantage.So my answer is, to quote the truism - "It depends"! However, I am only suggesting in these articles, specifically for developers who tend to avoid documentation, that there are tools and techniques that can help you do this fastidious part of a project, without too much pain.Regards,Adam

RE: Extended Properties Introduction

Adam Aspin — Thu, 17 Mar 2011 08:25:17 GMT

Hi ACinKC,Interesting that you should suggest this - it is a way I have used for a while - and it is so much easier to use a nice columnar structure to hold all the data. Also Excel is wonderful when copying the endlessly repeated parameters used for hundreds of metadata additions.I manage to avoid VBA with Excel by copying data extracted from the metadata tables and then concatenating data from multiple columns, and then copying & pasting the result into SSMS - though I have a VBA technique on show in the next article, but it it Word-based.Adam

RE: Extended Properties Introduction

ACinKC — Thu, 17 Mar 2011 08:15:35 GMT

Nicely done; looking forward to the rest of the articles.I created an Excel/VBA solution for my current gig that read in the metadata for the tables and columns into a workbook (one worksheet per table). We then handed that to the analysts/worksmiths to have the descriptions crafted. Once they were done, another button in the workbook parsed through all the Description columns and created or updated the Extended Properties for the objects. Turned out very simple and very little coding. Any updates to the database are easily handled by the VBA code, so maintenance is a breeze (except for the analysts/wordsmiths who have to do the actual updating in the Excel file!). It's a two-way tool, creating the Data Dictionary (in Excel) from the database and using the same tool in turn to update the EP values in the database.

RE: Extended Properties Introduction

Alexander Kuznetsov — Thu, 17 Mar 2011 07:53:19 GMT

Adam,Alternatively, I can just store all I want in my own table, and then I can work with sets of rows, search on any criteria I wish etc., and the next release is less likely break anything. What would be the disadvantages of rolling my own solution, except for referential integrity?

RE: Extended Properties Introduction

Adam Aspin — Thu, 17 Mar 2011 07:22:56 GMT

Thanks Jeff - praise from a leading light like yourself makes my day!There are three more articles to come (one a week for 3 weeks) which, hopefully, will help newbies not only add, but also extract and update this metadata in a comprehensive manner.Adam

RE: Extended Properties Introduction

Jeff Moden — Thu, 17 Mar 2011 07:00:49 GMT

Superb article, Adam. It's loaded with info and an easy read to boot. I like your writing style a lot!This is going into my "must read" locker for newbies. Thanks for taking the time to write it.

RE: Extended Properties Introduction

Adam Aspin — Thu, 17 Mar 2011 05:16:21 GMT

I quite agree - and the next article addresses this problem, in as much as I can.

RE: Extended Properties Introduction

P Jones — Thu, 17 Mar 2011 04:58:22 GMT

Getting extended properties in is easy enough especially with a bit of help from scripting. It is getting them out again in the form of meaningful documents that is the hard part.

RE: Extended Properties Introduction

Adam Aspin — Thu, 17 Mar 2011 04:21:03 GMT

Doh! Speling wil be my undiong! - Thanks Steve!

RE: Extended Properties Introduction

steve.casey — Thu, 17 Mar 2011 04:07:33 GMT

I fear the spell-checker has struck! You have "ellipse button" where you meant to say "ellipsis button"...

RE: Extended Properties Introduction

Vedran Kesegic — Thu, 17 Mar 2011 02:26:43 GMT

You can easily view column comments with free plugin called XDetails.Here is screenshot (Description is field with column comments):[img]https://pbs.twimg.com/media/A2Y6hZkCcAAZOpy.png:large[/img]You can download it here: [url=http://www.sqlxdetails.com]www.sqlxdetails.com[/url]

Extended Properties Introduction

Adam Aspin — Thu, 17 Mar 2011 00:01:08 GMT

Comments posted to this topic are about the item [B]Extended Properties Introduction[/B]