OT: Database Documentation Strategy

I'm creating a new SQL Server 2005 database with about 110 tables, some of
which have dozens of columns. I would like to document this database. What I
mean by that is I want a document that contains at least the following:
1. Each table name, and each column name per table.
2. For each column: data type, length, default value (if any), constraints
3. For each table: primary key column(s), indexes, foreign key constraints.
4. Brief text description of the data stored in the table, and business use
of the data (where not obvious).
Before I go and create an Excel spreadsheet that contains all of the above I
was wanting to know what some of you do. Are you all using Excel for this
sort of thing? Is there something more efficient? If so, what is it or what
do you do or recommend? Can I automate some of this?
FWIW: I'm already documenting the data model (tables and relationships etc)
in a Visio diagram. Now I need to document the table detail.
Thanks!Mark (A@.B.COM) writes:
> I'm creating a new SQL Server 2005 database with about 110 tables, some
> of which have dozens of columns. I would like to document this database.
> What I mean by that is I want a document that contains at least the
> following:
> 1. Each table name, and each column name per table.
> 2. For each column: data type, length, default value (if any), constraints
> 3. For each table: primary key column(s), indexes, foreign key
> constraints.
> 4. Brief text description of the data stored in the table, and business
> use of the data (where not obvious).
> Before I go and create an Excel spreadsheet that contains all of the
> above I was wanting to know what some of you do. Are you all using Excel
> for this sort of thing? Is there something more efficient? If so, what
> is it or what do you do or recommend? Can I automate some of this?
In our shop we use PowerDesigner from Sybase. This is a full-blown
data-modelling tool. The way I use it is that I first define the tables
in PowerDesigner and then generate a script from PowerDesigner that I
then post-process with my own Perl tools to get files formatted and
structuted to my own liking. PowerDesigner permits you to enter comments
for tables and columns. I can also generate reports from the data model,
either in RTF or HTML. What we actually use is the HTML reports, and
to make them a little nicer, I have a post-processing tool here as well.
PowerDesigner permits you to configure the reports, so that you can decide
what goes into it and what does not.
Since you already have the database in place, you may want to avoid to
enter all data manually - stay , you can do reverse engineering. Once
you have the data model in shape, you should start working in the other
direction.
The main competitors to PowerDesigner are ERwin (from Computer Associates)
and Embrocadero. I have not looked very closely on them, but they should
have the same capabilities as PowerDesigner.
All these tools comes with quite a price tag, I should hasten to add.
But a single-user license of PowerDesigner with only Physical Atchitect
is certainly worth the money.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx