Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Extended Properties Introduction


Extended Properties Introduction

Author
Message
Adam Aspin
Adam Aspin
SSC-Enthusiastic
SSC-Enthusiastic (183 reputation)SSC-Enthusiastic (183 reputation)SSC-Enthusiastic (183 reputation)SSC-Enthusiastic (183 reputation)SSC-Enthusiastic (183 reputation)SSC-Enthusiastic (183 reputation)SSC-Enthusiastic (183 reputation)SSC-Enthusiastic (183 reputation)

Group: General Forum Members
Points: 183 Visits: 1028
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
Adam Aspin
Adam Aspin
SSC-Enthusiastic
SSC-Enthusiastic (183 reputation)SSC-Enthusiastic (183 reputation)SSC-Enthusiastic (183 reputation)SSC-Enthusiastic (183 reputation)SSC-Enthusiastic (183 reputation)SSC-Enthusiastic (183 reputation)SSC-Enthusiastic (183 reputation)SSC-Enthusiastic (183 reputation)

Group: General Forum Members
Points: 183 Visits: 1028
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
Bill Talada
Bill Talada
Old Hand
Old Hand (377 reputation)Old Hand (377 reputation)Old Hand (377 reputation)Old Hand (377 reputation)Old Hand (377 reputation)Old Hand (377 reputation)Old Hand (377 reputation)Old Hand (377 reputation)

Group: General Forum Members
Points: 377 Visits: 1808
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.
Leonel Umaña Araya
Leonel Umaña Araya
Grasshopper
Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)

Group: General Forum Members
Points: 13 Visits: 28
Thanks, excellent article. I'll be waiting for the next ones. :-D


Leonel E. Umaña Araya
leo_umana@hotmail.com
Adam Aspin
Adam Aspin
SSC-Enthusiastic
SSC-Enthusiastic (183 reputation)SSC-Enthusiastic (183 reputation)SSC-Enthusiastic (183 reputation)SSC-Enthusiastic (183 reputation)SSC-Enthusiastic (183 reputation)SSC-Enthusiastic (183 reputation)SSC-Enthusiastic (183 reputation)SSC-Enthusiastic (183 reputation)

Group: General Forum Members
Points: 183 Visits: 1028
Hi Bill,

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

Adam
Cade Roux
Cade Roux
SSC-Enthusiastic
SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)

Group: General Forum Members
Points: 128 Visits: 491
I have a presentation I've given a couple times related to metadata and extended properties:

http://code.google.com/p/caderoux/wiki/LeversAndTurtles

Using 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.
YSLGuru
YSLGuru
SSC Eights!
SSC Eights! (959 reputation)SSC Eights! (959 reputation)SSC Eights! (959 reputation)SSC Eights! (959 reputation)SSC Eights! (959 reputation)SSC Eights! (959 reputation)SSC Eights! (959 reputation)SSC Eights! (959 reputation)

Group: General Forum Members
Points: 959 Visits: 1659
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.

Kindest Regards,

Just say No to Facebook!
Alexander Kuznetsov
Alexander Kuznetsov
SSC Veteran
SSC Veteran (245 reputation)SSC Veteran (245 reputation)SSC Veteran (245 reputation)SSC Veteran (245 reputation)SSC Veteran (245 reputation)SSC Veteran (245 reputation)SSC Veteran (245 reputation)SSC Veteran (245 reputation)

Group: General Forum Members
Points: 245 Visits: 824
Hi Adam,

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

AK
Sal Young
Sal Young
SSC-Enthusiastic
SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)

Group: General Forum Members
Points: 117 Visits: 367
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.


Sal Young
MCITP Database Administrator
stevemc
stevemc
SSC Journeyman
SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)

Group: General Forum Members
Points: 99 Visits: 666
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search