sql server programming: 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
To script out the structure of the database ( objects, columns, indexes...)
you can use either SMO Objects library or System Catalog views
See
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/323ac9ea-fc52-4b8c-8a7e-e0e44f8ed86c.htm






[quoted text, click to view]

Mark (A@B.COM) writes:
[quoted text, click to view]

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 cool, 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/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at