Re: database schema documentation

Maciej Gawinecki (mgawinecki@xxxxxxxxx) writes:
1. Just to check: Are this comments preserved in database when
executing CREATE TABLE, ALTER TABLE commands ?

Only if the are with the text for a CHECK constraint. That is, SQL
Server does not store the source code the CREATE TABLE statement
anywhere, it only stores the result of it. CHECK constraints are
an exception, because for these SQL Server to store the text.

Overall, I think the best model is to view everything stored in the
database as binaries, even if it in some cases the binaries are very
faithful copies of the original.

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?

Since SQL Server uses system stored procedures to supply extended
properties, it's a safe bet to assume that it is proprietary. I don't
know about other products.

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?)

Personally, I use PowerDesigner from Sybase which is a full-fledged
modelling tool. Alas, the price tag is full-fledged too.

An advantage with such a tool is that the documentation is stored
seprately from the database, which means that you can have one instance
of the documentation, but many instances of the databasees without a
problem.

A cheaper solution is SQLDoc from Red Gate. Essentially, they give you a
graphical interface to extended properties, and also a vehicle to produce
a document from it. I haven't checked, but it is not unlikely that
ApexSQL has something similar.

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

Powerdesigner is able to do RTF and HTML. I happen to have an example
of the latter on
http://www.sommarskog.se/AbaPerls/doc/database/abaperls.html,
although this is post-processed by my own tool, to make it a little
less verbose.


--
Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

.

Relevant Pages

  • RE: Backups have Shadow Copy Problems
    ... and restarted the server. ... suggested and changed the recovery model to simple on the one database called ... I understand the issue to be: the backup task failed ... You back up data from a volume that contains a Microsoft SQL Server ...
    (microsoft.public.windows.server.sbs)
  • Re: upsizing to sql 2005
    ... the word SERVER in it, ... You can access to the database by multiple means (Access, ... and how does it update the SQL database with the new records in Access? ... Query Name: Arcadia - ARC ...
    (microsoft.public.access.queries)
  • Re: Linked Tables in Access
    ... any use of SQL Passthru, Linked Tables or any other use of MDB / MDE ... server, or would I also need to convert *those* queries to passthrough ... I've been trying to understand why Access database files become corrupt. ...
    (microsoft.public.access.adp.sqlserver)
  • Re: Trouble Getting VS.Net 2003 WalkThroughs MSDE Connection
    ... Config Tool of SQL Server? ... > link to download the PUBs database. ... >>> Setup and they directed me to install MSDE and they attached a ...
    (microsoft.public.sqlserver.msde)
  • Re: Leveling by ID vs. "Standard"
    ... of this trade called Project Management. ... a database for the "Project Tables," ... to write reports on data from the server database. ... product supporting queries created with SQL. ...
    (microsoft.public.project)