Industrial-strength database documentation using Extended Properties

  • Adam Aspin

    SSCarpal Tunnel

    Points: 4805

    Comments posted to this topic are about the item Industrial-strength database documentation using Extended Properties

  • Charles Kincaid

    SSChampion

    Points: 13593

    Great article and series.

    I am curious though. You use "Description" and SSMS for columns uses "MS_Description". Is that because you are intending the article to be a teaching tool and want to avoid stepping on things?

    ATBCharles Kincaid

  • Adam Aspin

    SSCarpal Tunnel

    Points: 4805

    Hi Charles,

    I am using "Description" for a couple of reasons - firstly to show that you can classify metadata comments by your own categorisation types (I have used DataLineage, ToolTip as well) and secondly because I have noticed that views in SSMS use the MS_ prefix - and this way I can avoid MS-based data when outputting.

    I don't know if this is good or bad, approved or not - but it seems to work!

    Thanks for the feedback!

    Adam

  • Vedran Kesegic

    SSCrazy Eights

    Points: 9180

    XDetails plugin uses MS_Description, as it is standard in SSMS.

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

    Ten Centuries

    Points: 1201

    This free tool uses MS_Description as well...

    http://www.DBGizmo.net

  • SQLRNNR

    SSC Guru

    Points: 281233

    Thanks for the article. Great content.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • fahey.jonathan

    Hall of Fame

    Points: 3564

    Thanks for the great series of articles. I started using the extended properties when I got a tool that would create database documentation based on those properties.

    I found that I had many columns with the same name and were really the same item in various tables. For example, many tables had the "AccountID" column, and all were copies of the same thing (primary key in the Account table, foreign key in all other tables). When writing the statement that creates the sp_addextendedproperty, I found it helpful to try to locate another field with the same name and use that as the default value. That allowed me to save a lot of retyping. Of course, I had to review each generated statement for accuracy and make changes as necessary, but it was better than having to look up what I created for the previous tables and retype or copy/paste every time. For my defaults, I used the most-frequently-used description for columns with the same name.

    Again, thanks for the thorough explanation and the helpful examples.

  • Adam Aspin

    SSCarpal Tunnel

    Points: 4805

    Hi Jonsathan,

    You have good ideas there - cross-referencing columns with the same name and reusing repeatable descriptions. Thanks for sharing that.

    Adam

  • SSIS Guy

    SSCommitted

    Points: 1667

    Here is one more Free Tool to document Sql Server

  • xyzmel

    SSC Rookie

    Points: 30

    Very informative series of articles. What would be helpful to me would be the ability to combine results from more than one database. We typically have a pair of databases for an application (data and code), and we have quite a few pairs. It would be nice if there could be a single instance of each stored procedure that could be executed against any database. Is there a way to do that?

Viewing 10 posts - 1 through 10 (of 10 total)

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