Last week I was working for a client, designing a SQL Server 2005 database. I prefer to do my design, building the actual tables in SQL Management Studio. I know some purest frown on this as not truly a design phase, but rather implementing as you go. However, in my opinion, it is just as much effort to build a data model in a modeling tool (or Visio) as it is to build the real thing. By doing it right in SQL Server, I can always get a diagram out later and don't lose any cycles I would otherwise lose by first designing and then later implementing the same thing.
So anyway, I've got my data model, now I need to write a nice design document for my client. I started by writing a SQL query to extract the basic column data from the database. In a few minutes I put this together.
select t.name, c.name, typ.name, c.length
from syscolumns c, sysobjects t, systypes typ
where c.id = t.id
and c.type = typ.type and c.xtype = typ.xtype
and typ.status = 0
and t.xtype = 'U'
and not t.name = 'sysdiagrams'
order by t.name, c.colorder
As I started to look for ways to query whether a column was nullable, had a default, etc; I started to think about writing a little tool I could reuse to create a core data model document.
So maybe it is because I prefer to build things myself (build vs buy tendency), but I realized that someone else has probably already done this. Enter DBScribe. What a fantastic tool! I'm glad I didn't spend more than a few minutes on building something myself.
You can find this tool at www.leadum.com. Besides SQL Server, they support many other database platforms, as well as a tool to document Active Directory. I've used this tool for a few days and cannot stop raving about it. Beside the amazing flexiblity of including or excluding just about anything you desire in the resulting document, it also works with the new features in SQL 2005 that allow you to enter extended properties (meta data) for the database objects. My big fear with putting much time into a tool was that I would go in and add all sorts of custom comments and descriptions for each table and column, and then need to do it again (copy and paste) if I made significant changes and wanted to run a new document generation. Well, DBScribe allows you to enter descriptions and it stores it in the extended properties. This allows you rerun the document generator as many time as you like and never lose your comments.
This tool is well worth a look and at less than a $100, it is a steal. It saved my client and myself, far more that the cost of this tool. Give their 30 day trial a test run and you'll be a believer too.