Database schema documentation
dzieciou
Posts: 2
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
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
Comments
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
Product Manager
Redgate Software
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
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
Product Manager
Redgate Software