How to quickly document your SQL Server database

Do you need to document your SQL Server database? While you could do this by hand, it's worth investing in a utility called the SQL Documentation Tool. This handy little tool will connect to a Microsoft SQL Server instance and quickly document the databases found in that database.

The SQL Documentation Tool automatically creates database documentation in a number of formats:

• HTML format is useful for placing a handy database documentation reference on your company's or your client's Intranet.
• HTML Help format is the standard Microsoft Windows help file format, and allows the database documentation to be contained within a single file. HTML Help database documentation produced by the SQL Documentation Tool also has a nice index of database entities, and is even fully searchable!
• Microsoft Word/RTF format database documentation is great for printing and showing the boss!
• Text format documentation can form the basis of other documentation that you might be told to write for a database.

Microsoft SQL Server is a widely used relational database management system (RDMS). There are various versions, from SQL Server 7.0 to SQL Server 2000, SQL Server 2005 and SQL Server 2008.

So what do you need to document in a SQL Server database?

Well obviously the first thing to document is Tables. Documentation at the very least should state the table name. It's also a good idea to list the table's columns. Columns can have a number of properties but when creating database documentation it's especially useful to include the table column datatypes, and the length of the data allowed if the column is a text field such as a varchar or char field.

As well as SQL Server tables, it is also a good idea to document the database's Views. As with tables, the views have columns which can be detailed in the documentation. Often it is useful to show the SQL statement associated with the view (i.e. the Create View statement).

Many SQL Server databases make use of stored procedures, so it is useful to document these as well. It's useful to know the stored procedure's input parameters, with their data types, and whether they are input or output parameters. The database documentation could also make use of the stored procedure's CREATE PROCEDURE SQL script.

What else? Well the SQL Server database is obviously a relational database, so it's vital to be able to document database relationships - these include the relationships between tables, and also the dependency information. It's nice to be able to document database dependency information in order to find out what the impact would be of changing a certain table or stored procedure.

Don't forget that SQL Server also supports Functions.

Some databases have additional functionality associated with them. It is useful to be able to document the Full Text Indexes associated with a database.

SQL Server also has a few items that are non database-specific. These include Jobs and DTS Packages. The SQL Documentation Tool can document both of these.

If that all sounds hard work, well it's because it is hard work! Thankfully there are tools that will completely automate the process of documenting SQL Server databases. The SQL Documentation Tool documents all versions of SQL Server, and makes boring technical writing projects fun again.

So if you want to save your valuable time then give the SQL Documentation Tool a go (a free trial version is available).

A code documentor for .NET Framework C# and VB.NET source code is also available.