Post reply

Extended Properties Introduction

  • March 17, 2011 at 12:01 am

    #236998

    Comments posted to this topic are about the item Extended Properties Introduction

  • March 17, 2011 at 2:26 am

    #1299822

    You can easily view column comments with free plugin called XDetails.

    Here is screenshot (Description is field with column comments):

    You can download it here: www.sqlxdetails.com

    _____________________________________________________
    Microsoft Certified Master: SQL Server 2008
    XDetails Addin - for SQL Developers
    blog.sqlxdetails.com - Transaction log myths

  • March 17, 2011 at 4:07 am

    #1299841

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

  • March 17, 2011 at 4:21 am

    #1299847

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

  • March 17, 2011 at 4:58 am

    #1299865

    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.

  • March 17, 2011 at 5:16 am

    #1299867

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

  • March 17, 2011 at 7:00 am

    #1299911

    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.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • March 17, 2011 at 7:22 am

    #1299921

    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

  • March 17, 2011 at 7:53 am

    #1299944

    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?

  • March 17, 2011 at 8:15 am

    #1299957

    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.

  • March 17, 2011 at 8:25 am

    #1299966

    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

  • March 17, 2011 at 9:06 am

    #1299990

    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

  • March 17, 2011 at 9:07 am

    #1299991

    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.

  • March 17, 2011 at 9:12 am

    #1299994

    Thanks, excellent article. I'll be waiting for the next ones. 😀

    • This reply was modified 5 years, 8 months ago by  Leonel Umaña Araya.
    • This reply was modified 1 year, 3 months ago by  Leonel Umaña Araya.


    Leonel E. Umaña Araya
    leo_umana@hotmail.com

  • March 17, 2011 at 9:32 am

    #1300011

    Hi Bill,

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

    Adam

Viewing 15 posts - 1 through 15 (of 49 total)

You must be logged in to reply to this topic. Login to reply