I am in the (fun!) process of documenting an application I created nearly 5 years ago, in which the majority of the functionality is controlled by the database (it's a business workflow automation tool).

Reverse engineering substantially reduces the size of the task - Visio's Database > Reverse Engineer... command is a real time saver when you have foreign key constraints defined, makes generation of an ERD no more complicated than arranging all the boxes so that none (or few!) of the relationship lines cross!

A little utility contributed to the Code Project website makes the process of creating table and field documentation much easier: http://www.codeproject.com/KB/database/sqldoc.aspx

This utility can be pointed at a SQL server database, and will generate an HTML document listing all the tables and fields in the database, together with data types, nullable, default values and description fields from the db schema.

It uses an XSLT transformation to convert the xml generated to an HTML document. This can be edited so that it's closer to final requirements - I have updated the XSLT to place anchors on each table name, and links at the top of the file to each table.

This makes it a much less arduous task to document each table and field - I can edit the file in dreamweaver and just add descriptions for each field and table.

I've always been a fan of Views within SQL Server - I've found them especially useful when creating applications which need to be able to access data from external sources, without necessarily being aware of where that data came from - or even of its precise format.

One feature which I have found especially useful, is the ability to select and join to data from within a different database, from within a view.

This can be done as follows:

[More]

I've always been a fan of Views within SQL Server - I've found them especially useful when creating applications which need to be able to access data from external sources, without necessarily being aware of where that data came from - or even of its precise format.

One feature which I have found especially useful, is the ability to select and join to data from within a different database, from within a view.

This can be done as follows:

So specifying an explicit database name in front of the schema name causes the data to be retrieved from the named DB, on the same server, rather than from the db in which the view is placed. I believe it is also possible to specify a database which is on a different server, using Linked Servers - but I've never had a requirement to check this out so far.

What's great about this, is that if I reference this view rather than the tables directly within my application / stored procedures, I can change where the data is coming from without impact on the application. This has been particularly useful in the past when a system is reliant upon external data, but needs to be developed in isolation. I can create a table in my local DB which mirrors the structure of the remote data source, and point my view at that - and then when the application is ready for integration, I can update the view to point to the remote DB.

[More]