Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12345»»»

Extended Properties Introduction Expand / Collapse
Author
Message
Posted Thursday, March 17, 2011 8:25 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, September 18, 2014 7:03 AM
Points: 81, Visits: 903
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
Post #1079679
Posted Thursday, March 17, 2011 9:06 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, September 18, 2014 7:03 AM
Points: 81, Visits: 903
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
Post #1079770
Posted Thursday, March 17, 2011 9:07 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 1:49 PM
Points: 138, Visits: 879
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.
Post #1079771
Posted Thursday, March 17, 2011 9:12 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, March 17, 2014 3:05 PM
Points: 8, Visits: 23
Thanks, excellent article. I'll be waiting for the next ones.


Leonel E. Umaña Araya
leo_umana@hotmail.com
Post #1079774
Posted Thursday, March 17, 2011 9:32 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, September 18, 2014 7:03 AM
Points: 81, Visits: 903
Hi Bill,

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

Adam
Post #1079792
Posted Thursday, March 17, 2011 10:02 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, September 4, 2014 8:26 AM
Points: 109, Visits: 490
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.
Post #1079825
Posted Thursday, March 17, 2011 10:42 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Thursday, September 18, 2014 4:51 PM
Points: 889, Visits: 1,550
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!
Post #1079854
Posted Thursday, March 17, 2011 10:54 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Saturday, May 24, 2014 7:58 PM
Points: 219, Visits: 824
Hi Adam,

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

AK
Post #1079865
Posted Thursday, March 17, 2011 11:09 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Sunday, August 31, 2014 7:15 AM
Points: 108, Visits: 316
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
Post #1079877
Posted Thursday, March 17, 2011 11:26 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, September 1, 2014 8:48 AM
Points: 94, Visits: 568
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.
Post #1079896
« Prev Topic | Next Topic »

Add to briefcase ««12345»»»

Permissions Expand / Collapse