Auto Generate Your Database Documentation

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

    .

  • 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.

  • 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

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

    Thanks!

  • 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

  • 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

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

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

  • Thanks for the good article.

  • 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.

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

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

  • 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".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

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

  • 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;

    .

  • 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.

    .

  • 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".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

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

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

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