SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Extended Properties Introduction


Extended Properties Introduction

Author
Message
Adam Aspin
Adam Aspin
Say Hey Kid
Say Hey Kid (661 reputation)Say Hey Kid (661 reputation)Say Hey Kid (661 reputation)Say Hey Kid (661 reputation)Say Hey Kid (661 reputation)Say Hey Kid (661 reputation)Say Hey Kid (661 reputation)Say Hey Kid (661 reputation)

Group: General Forum Members
Points: 661 Visits: 1032
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
Say Hey Kid
Say Hey Kid (661 reputation)Say Hey Kid (661 reputation)Say Hey Kid (661 reputation)Say Hey Kid (661 reputation)Say Hey Kid (661 reputation)Say Hey Kid (661 reputation)Say Hey Kid (661 reputation)Say Hey Kid (661 reputation)

Group: General Forum Members
Points: 661 Visits: 1032
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
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1618 Visits: 2039
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
SSC Rookie
SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)

Group: General Forum Members
Points: 37 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
Say Hey Kid
Say Hey Kid (661 reputation)Say Hey Kid (661 reputation)Say Hey Kid (661 reputation)Say Hey Kid (661 reputation)Say Hey Kid (661 reputation)Say Hey Kid (661 reputation)Say Hey Kid (661 reputation)Say Hey Kid (661 reputation)

Group: General Forum Members
Points: 661 Visits: 1032
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 Veteran
SSC Veteran (250 reputation)SSC Veteran (250 reputation)SSC Veteran (250 reputation)SSC Veteran (250 reputation)SSC Veteran (250 reputation)SSC Veteran (250 reputation)SSC Veteran (250 reputation)SSC Veteran (250 reputation)

Group: General Forum Members
Points: 250 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
SSCrazy
SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)

Group: General Forum Members
Points: 2358 Visits: 1665
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
Mr or Mrs. 500
Mr or Mrs. 500 (509 reputation)Mr or Mrs. 500 (509 reputation)Mr or Mrs. 500 (509 reputation)Mr or Mrs. 500 (509 reputation)Mr or Mrs. 500 (509 reputation)Mr or Mrs. 500 (509 reputation)Mr or Mrs. 500 (509 reputation)Mr or Mrs. 500 (509 reputation)

Group: General Forum Members
Points: 509 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 (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)

Group: General Forum Members
Points: 139 Visits: 374
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-Enthusiastic
SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)

Group: General Forum Members
Points: 156 Visits: 681
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