Database schema documentation

Documents SQL Server 2000, 2005 and 2008 databases.


Moderators: David Atkinson, david connell, Charles Brown

Database schema documentation

Postby dzieciou » Mon Feb 09, 2009 11:10 am

The ISO-standard SQL specifies double dash, --, as a single line comment identifier. Example:

SELECT * FROM Inventory WHERE category = 2 -- Category 2 is electronics

Additionally, the database system may allow for C-style /* ... */ multi-line comments, as well.

1. Just to check: Are this comments preserved in database when executing CREATE TABLE, ALTER TABLE commands ?

2. Some databases (MSSQL) allows to annotate schema elements (tables, columns) with additional documentation, which is then stored in the database. Is it part of the SQL standard? What other databases allows for that?

3. I've seen many databases schemas with external documentation, generated with different documentation tools. I'm particularly interested in tools that support a designer to annotate particular schema elements and generate external documentation (PDF, HTML, DOC ?). What are the most popular tools for schema documentation? (any survey on that?)

4. What are the most popular formats for documentation output? Any examples, I can browse to see how they are formated, structured?

Thanks,
Maciej
dzieciou
 
Posts: 1
Joined: Mon Feb 09, 2009 10:59 am

Postby David Atkinson » Mon Feb 09, 2009 1:38 pm

Hi,
Thanks for the questions. I'll do my best to answer them, but I think it would be more interesting if others joined the discussion.

1. No. Comments are only preserved in the database for textual objects, which include Stored Procedures, Functions and Views.

2. SQL Server's MS_Description extended property is SQL-Server-only as far as I'm aware.

3. You're essentially looking for an easy tool/method to annotate your schema elements with documentation and then publish this as a document. SQL Doc will allow you to add descriptions to objects. Have you tried this? If this isn't where you would want this functionality to be, where would you prefer it?

4. Hopefully some SQL Doc users will be able to answer this one for you!

Kind regards,

David Atkinson
Red Gate Software
David Atkinson
 
Posts: 1124
Joined: Mon Dec 05, 2005 4:54 pm
Location: Twitter: @dtabase

Postby fatherjack » Mon Feb 09, 2009 4:44 pm

Hi,

For my purposes I have used MS Word and PDF for distributing/sharing database object definitions. They seem to be the easiest way to generate a technical document, MSWord with its 'clickable' table of contents is great. PDF is obviously a format that everyone can read regardless of installed software.

For Disaster Recovery/Business Continuity I create and store database scripts in SQL file format on our network. These obviously could change database objects if executed so I only distribute these between DBAs as necessary.

I'm afraid I dont have any examples - David/Red-Gate, can you come up with some sample output? Maybe from AdventureWorks?

HTH

Jonathan
-----------------------------------------
Senior DBA
Careers South West Ltd
fatherjack
 
Posts: 224
Joined: Tue Mar 13, 2007 12:33 pm
Location: Cornwall

Re:

Postby David Atkinson » Mon Feb 09, 2009 4:58 pm

fatherjack wrote:Hi,
I'm afraid I dont have any examples - David/Red-Gate, can you come up with some sample output? Maybe from AdventureWorks?
Jonathan


What I'd suggest for Maciej is to download SQL Doc and try it out. SQL Doc supports MSWord, HTML and CHM outputs. The tool is fully functional for 14 days.

Maciej, we'd love to get your feedback on the tool.

Kind regards,

David
David Atkinson
 
Posts: 1124
Joined: Mon Dec 05, 2005 4:54 pm
Location: Twitter: @dtabase


Return to SQL Doc 1

Who is online

Users browsing this forum: No registered users and 0 guests