Document Your Database

  • Vasant Raj

    SSCommitted

    Points: 1835

    Comments posted here are about the content posted at http://www.sqlservercentral.com/columnists/rVasant/2784.asp

  • Albie van Rensburg

    SSC Rookie

    Points: 33

    Decent advice.

    Most design methodologies (especially agile methods) have heavy documentation requirements for a project - not only does it ease position transfer such as job replacements, but also, it really helps to have documentation of stuff you wrote yourself a couple of months/years ago when you need to work on it again. Good naming conventions of columns and variables also go a long way to clearing up understanding of old code/tables.

    I like the ability to comment on a table column directly. The closer to the actual usage a comment is, the more valuable I rate it.

  • shashi kant

    SSCommitted

    Points: 1659

    Good Thing you have been introduced

    it really help in documentation ... related thing........

     

     

  • Stefan K

    SSCommitted

    Points: 1786

    Hi,

    about Tools: I made good experiences with Enterprise Architect from Sparx Systems . It is a software modelling tool. http://www.sparxsystems.com.au/

    You can import DDL Schemas by using OLEDB and ODBC connections to many database systems. The tool reads the extended Properties from SQL Server Objects and from others (DB2).

    You can edit the DDL with a GUI and export DDL scripts.

    Best regards,

    Stefan


    Kindest Regards,

    SK

  • Tim Mackey

    SSC Rookie

    Points: 42

    From my experience it's not just about documenting the objects, but how they're accessed and their purpose; and ensuring that the documentation is up to date.  MS Word, as the author suggests, requires someone to keep it up to date; and we all know how well that turns out.  There are many tools out there, all with their good points, but I personally wouldn't ever consider one which didn't offer me the ability to run as part of a build, or nightly task.

    On the subject of tools; my shameless plug is for DBDocumentor from Pikauba Software (http://www.pikauba.com/DBDoc/details.htm).  It works from the SQL sources so it won't be for everyone, but the tradeoff is that you can get information about result sets and data accesses/modifications.  It also has support for SSRS RDL, so that's a good bonus.

    Cheers

     

     

     

  • Mark Harr

    SSCrazy Eights

    Points: 9760

    I recently saw a pretty good product for a affordable price.  SQLSpec from Elsasoft (http://www.elsasoft.org/) was actually written by a member here http://www.sqlservercentral.com/forums/userinfo.aspx?id=145142.  It does not do HTML Help files like ApexDoc, but seems to cover the features pretty well.  It can also help to document application access (though you have to put the links and info in via an additional XML file).  And for $50, it certainly more affordable than the others listed here.



    Mark

  • Charles Kincaid

    SSChampion

    Points: 13593

    I've been doing this for quite a while now.  Version before 2005 did not support extended properties for tables well.  I wanted a unified system.  So:

    CREATE TABLE [doc_Fields] (

      [TableName] [varchar] (50),

      [FieldName] [varchar] (50),

      [Comment] [varchar] (4000)

    ) ON [PRIMARY]

    I then put my field and table descriptions here.  If the field name is blank, or NULL, then Comment applies to the table.  A little grid based program to make the entry easy and one to generate an RTF file.

    Now ported to .Net and using the extended properties it serves us well.  The advantage of the extended properties is that when I generate create scripts the comments come along.  This is also a help to the UI teams.  They can use this to generate tool tips.

     

    ATBCharles Kincaid

  • Andy DBA

    SSCommitted

    Points: 1776

    This may not have been mentioned in the article because it's too obvious, but I consider a database relational diagram to be one of the most important pieces of documentation for any database.  I usually create one with only the "core" tables but detailed column information and another with all tables including "look-up"s but with less column detail.

    I also use the following sql to list tables, columns, and the "Description" text that EM's "Design Table" lets you enter.  I save the results to Excel and add the spreadsheet to my database documentation.

    Note: This works on SQL 2000.  I have not tried it on SQL 7.0 or 2005 (yet) but it probably needs adjusting because it uses system tables.

    --List tables, columns and column descriptions

    select SO.name as 'table', SC.name as field,

    ST.name as datatype, SC.length as 'size',

    sp.value as 'description'

    from syscolumns SC inner join

    sysobjects SO on SC.id = SO.id inner join

    systypes ST on ST.xusertype = SC.xusertype left join

    sysproperties sp on  sp.id = so.id and sp.smallid = SC.colid

    where SO.xtype = 'U' and SO.status > 0

    order by SO.name, SC.name

  • Andy DBA

    SSCommitted

    Points: 1776

    CORRECTION.  The prior posted sql may have duplicates.  The following should work better:

    --List tables, columns and column descriptions

    select SO.name as 'table', SC.name as field,

    ST.name as datatype, SC.length as 'size',

    sp.value as 'description'

    from syscolumns SC inner join

    sysobjects SO on SC.id = SO.id inner join

    systypes ST on ST.xusertype = SC.xusertype left join

    sysproperties sp on  sp.id = so.id and sp.smallid = SC.colid

    and sp.name = 'MS_Description'

    where SO.xtype = 'U' and SO.status > 0

    order by SO.name, SC.name

  • Charles Kincaid

    SSChampion

    Points: 13593

    You are right. Just tried it in 2005 and it fails.

    ATBCharles Kincaid

  • Charles Kincaid

    SSChampion

    Points: 13593

    I added this:

    select

    SO.name as 'table', SC.name as field,

    ST.name as datatype, SC.length as 'size',

    COALESCE

    (sp.value,df.Comment,'') as 'description'

    from

    syscolumns SC

    inner

    join sysobjects SO on SC.id = SO.id

    inner

    join systypes ST on ST.xusertype = SC.xusertype

    left

    join sysproperties sp on sp.id = so.id and sp.smallid = SC.colid and sp.name = 'MS_Description'

    left

    outer join doc_fields df on df.tablename = so.name and df.fieldname = SC.name

    where

    SO.xtype = 'U' and SO.status > 0

    order

    by SO.name, SC.colorder

    Now it looks into the extended properties or my table.

    ATBCharles Kincaid

  • Terrie

    Ten Centuries

    Points: 1293

    I keep a corresponding dictionary database for every database I design with descriptions of the tables, views and columns.  Good idea to add stored procs, I hadn't thought of that.  I'm required to document data classification (you know what's sensitive and what's not), so that's in there too.  For that last database I developed, all the columns are populated from a non-windows system so I added the source information too.  Now I can talk to the admins on that system in their terms if I need to.

  • Robert Bedick

    Old Hand

    Points: 310

     It's great that you mentioned the Apex product, but I would expect an "article" on the topic to do a more thorough compare-and-contrast of the many products that are out there to help a dba document his or her database. Just to mention some others:

    http://www.schematodoc.com/

    http://www.ag-software.com

    http://www.dbdesc.com

  • JAG-360157

    Valued Member

    Points: 56

    dbdesc (mentioned earlier in this thread) can generate Word 2003 and RTF documents among others. It uses XSL templates so the output can be fully customized.

    Additionally, it includes a built-in extended property editor to easily manage descriptions. In my opinion, extended properties are the perfect place to store your comments as they go together with your database.

  • David.Poole

    SSC Guru

    Points: 75296

    Don't forget Innovasys DocumentX. It covers SQL Server, Oracle, Access and also does .NET assemblies, Visual Studio projects, ActiveX and VBA.

    After using a proper tool I wouldn't use a word processor for documentation because

    • It is not easy to update
    • It is not intrinsically linked to the database so can be obsolete fast
    • Does not automatically cross reference to bookmarks within itself
    • Isn't a natural reference format (such as CHM or web ) for programmer
    • Is very bulky

    It sounds obvious but the purpose of documentation is to be read. There is a great skill in writing a complex document in such a way that it is easy to pick up the salient facts quickly.

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

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