Post reply

Auto Generate Your Database Documentation

  • February 24, 2016 at 10:31 pm

    #149266

    Comments posted to this topic are about the item Auto Generate Your Database Documentation

    .

  • February 25, 2016 at 7:06 am

    #1861044

    Extended properties are essential for good documentation, yes, but if you're adding them to the database itself (after the fact) you have a world of hurt just waiting for you.

    Why in the world wouldn't you add this information to the database modelling tool used to design the database and create the build script for it?

    That way the extended properties are A) properly maintained in a central "golden authority" and B) ALWAYS placed in the database as part of the creation script/alter scripts.

    Much safer, much more consistent, and never subject to "accidents" from hasty editing decisions.

  • February 25, 2016 at 7:13 am

    #1861047

    The single most helpful tool I don't see regularly used is a table count and date on all the tables, followed by a periodic grab of the data.

    Obviously a trace is going to tell you a lot of about what is going on, but the volume of the data is much more extensive.

    412-977-3526 call/text

  • February 25, 2016 at 9:59 am

    #1861153

    This makes documentation as easy as it can be and stores it in a logical place that anyone can find.

    Thanks!

  • February 25, 2016 at 10:47 am

    #1861170

    This is very useful information, thanks for posting this. One item that I have always struggled with documenting is the owner of logins, not users. But extended properties can't be created on logins. Do you have any suggestion for an elegant way to address this?

    Thanks

  • February 25, 2016 at 1:31 pm

    #1861239

    Why in the world wouldn't you add this information to the database modelling tool used to design the database and create the build script for it?

    Obviously that's the ideal situation, but when it doesn't happen, and you inherit the laziness of others, you have to start somewhere. Andy's suggestion is excellent but even it won't solve the problem of those who take shortcuts and leave others with a mess. That is an organisational culture problem, not a technical problem.

    There's a special place in hell for people who take credit/bonuses/promotions for passing off undocumented software as if it were a finished product. There is possibly no lower form of programmer.:exclamationmark:

    ...One of the symptoms of an approaching nervous breakdown is the belief that ones work is terribly important.... Bertrand Russell

  • February 25, 2016 at 5:08 pm

    #1861309

    Last I heard, Azure does not support extended properties. So this may only be an on-premises solution.

  • February 28, 2016 at 8:34 pm

    #1861713

    thanks for listening Andy! Appreciate the reference as well. great article!

  • March 9, 2016 at 4:52 pm

    #1864150

    Thanks for the good article.

  • March 19, 2016 at 2:00 am

    #1866251

    Thanks for the article. Very creative approach to make use of this hidden and under utilised tool. Documentation is among the last thing people think of when creating their objects and columns, if at all. Any documentation that may exist I find is lost in heaps of folders on some remote share. Here, the information is at the forefront.

    ----------------------------------------------------

  • November 17, 2017 at 1:23 am

    #1968467

    Very interesting approach! Thanks for sharing.
    One remark though: declare @value should probably read declare @val (in the first code fragment)
    Regards
    Thierry

  • November 17, 2017 at 8:18 am

    #1968532

    The code for the auto-generation of extended properties in this article seems to be just a regurgitation of properties that should be listed elsewhere.  It doesn't explain the purpose of the column at all and that's more of what's almost always missing and more of what is truly needed for a data dictionary.

    --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)

  • November 17, 2017 at 3:34 pm

    #1968609

    thierry.vandurme - Friday, November 17, 2017 1:23 AM

    Very interesting approach! Thanks for sharing.
    One remark though: declare @value should probably read declare @val (in the first code fragment)
    Regards
    Thierry

    Many thanks Thierry. You are absolutely correct, I think the end of the code fragment was cropped, it should read: 

    EXECUTE sys.sp_addextendedproperty @name = 'MS_Description', @value = @value;

    .

  • November 17, 2017 at 3:52 pm

    #1968612

    Jeff Moden - Friday, November 17, 2017 8:18 AM

    The code for the auto-generation of extended properties in this article seems to be just a regurgitation of properties that should be listed elsewhere.  It doesn't explain the purpose of the column at all and that's more of what's almost always missing and more of what is truly needed for a data dictionary.

    Hi Jeff,

    Many thanks for your comment. No, the code to generate the extended property create statements does not explain the purpose of each object or column. We explicitly want to avoid this, as mentioned in the article “It is possible to automate a description such as “this is column A on table dbo.B†but you will end up with some meaningless documentation that tells the reader very little. I wouldn’t recommend doing this.â€

    To create documentation that adds any value, we must manually describe the purpose of each object or column based on our business knowledge.
    The purpose of the generation script is threefold:

    1)      It only generates the sp_addextendedproperty statements for those objects or columns that do not already have an extended property.
    2)      Each extended property create statement includes the user and date. This adds value and creates extended properties with a consistent format.
    3)      It mass produces all the required extended property create statements and avoids having to write each individually or click around the GUI. Once we have the create statement, we must edit the description to clearly describe the purpose of the object and hit F5.

    Thanks again, Andy.

    .

  • November 17, 2017 at 5:23 pm

    #1968615

    Andy Jones, DBA - Friday, November 17, 2017 3:52 PM

    Jeff Moden - Friday, November 17, 2017 8:18 AM

    The code for the auto-generation of extended properties in this article seems to be just a regurgitation of properties that should be listed elsewhere.  It doesn't explain the purpose of the column at all and that's more of what's almost always missing and more of what is truly needed for a data dictionary.

    Hi Jeff,

    Many thanks for your comment. No, the code to generate the extended property create statements does not explain the purpose of each object or column. We explicitly want to avoid this, as mentioned in the article “It is possible to automate a description such as “this is column A on table dbo.B†but you will end up with some meaningless documentation that tells the reader very little. I wouldn’t recommend doing this.â€

    To create documentation that adds any value, we must manually describe the purpose of each object or column based on our business knowledge.
    The purpose of the generation script is threefold:

    1)      It only generates the sp_addextendedproperty statements for those objects or columns that do not already have an extended property.
    2)      Each extended property create statement includes the user and date. This adds value and creates extended properties with a consistent format.
    3)      It mass produces all the required extended property create statements and avoids having to write each individually or click around the GUI. Once we have the create statement, we must edit the description to clearly describe the purpose of the object and hit F5.

    Thanks again, Andy.

    Exactly.  It's going to take a human to document the columns of the table. 

    BTW... I only pointed out the part I disagreed with.  Thank you very much for stepping up and writing an article!

    --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)

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

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