SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Document Your Database


Document Your Database

Author
Message
Vasant Raj
Vasant Raj
SSC Eights!
SSC Eights! (827 reputation)SSC Eights! (827 reputation)SSC Eights! (827 reputation)SSC Eights! (827 reputation)SSC Eights! (827 reputation)SSC Eights! (827 reputation)SSC Eights! (827 reputation)SSC Eights! (827 reputation)

Group: General Forum Members
Points: 827 Visits: 137
Comments posted here are about the content posted at http://www.sqlservercentral.com/columnists/rVasant/2784.asp
Albie van Rensburg
Albie van Rensburg
Grasshopper
Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)

Group: General Forum Members
Points: 11 Visits: 28
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
shashi kant
SSC Eights!
SSC Eights! (932 reputation)SSC Eights! (932 reputation)SSC Eights! (932 reputation)SSC Eights! (932 reputation)SSC Eights! (932 reputation)SSC Eights! (932 reputation)SSC Eights! (932 reputation)SSC Eights! (932 reputation)

Group: General Forum Members
Points: 932 Visits: 581

Good Thing you have been introduced

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


Stefan K
Stefan K
Right there with Babe
Right there with Babe (782 reputation)Right there with Babe (782 reputation)Right there with Babe (782 reputation)Right there with Babe (782 reputation)Right there with Babe (782 reputation)Right there with Babe (782 reputation)Right there with Babe (782 reputation)Right there with Babe (782 reputation)

Group: General Forum Members
Points: 782 Visits: 44

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
Tim Mackey
SSC Rookie
SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)

Group: General Forum Members
Points: 28 Visits: 6

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
Mark Harr
SSCarpal Tunnel
SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)

Group: General Forum Members
Points: 4876 Visits: 199
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
Charles Kincaid
SSCertifiable
SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)

Group: General Forum Members
Points: 5769 Visits: 2384

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
Andy DBA
SSC Eights!
SSC Eights! (870 reputation)SSC Eights! (870 reputation)SSC Eights! (870 reputation)SSC Eights! (870 reputation)SSC Eights! (870 reputation)SSC Eights! (870 reputation)SSC Eights! (870 reputation)SSC Eights! (870 reputation)

Group: General Forum Members
Points: 870 Visits: 779

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
Andy DBA
SSC Eights!
SSC Eights! (870 reputation)SSC Eights! (870 reputation)SSC Eights! (870 reputation)SSC Eights! (870 reputation)SSC Eights! (870 reputation)SSC Eights! (870 reputation)SSC Eights! (870 reputation)SSC Eights! (870 reputation)

Group: General Forum Members
Points: 870 Visits: 779

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
Charles Kincaid
SSCertifiable
SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)

Group: General Forum Members
Points: 5769 Visits: 2384
You are right. Just tried it in 2005 and it fails.

ATBCharles Kincaid
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum







































































































































































SQLServerCentral


Search