Save yourself from despair with full database documentation

Your ability to document thoroughly will determine  your efficiency and productivity when it comes to managing your SQL Server databases. Aside from the question of productivity, there’s always the concern of having to come across with problems relating to the unfamiliarity of vendor databases (this is particularly true with third-party applications).

Commercial products usually come with proper documentation. But it is sad to note that not many of them would provide customers with enough details appropriate to their needs.

Sure, you can build your own procedures and write all the necessary commands to extract metadata from your databases but that will probably take a lot of effort and patience.

Take a look at this sample query:

SELECT u.name + '.' + t.name AS [table] ,
td.value AS [table_desc] ,
c.name AS [column] ,
cd.value AS [column_desc]
FROM sysobjects t
INNER JOIN sysusers u ON u.uid = t.uid
LEFT OUTER JOIN sys.extended_properties td ON td.major_id = t.id
AND td.minor_id = 0
AND td.name = 'MS_Description'
INNER JOIN syscolumns c ON c.id = t.id
LEFT OUTER JOIN sys.extended_properties cd ON cd.major_id = c.id
AND cd.minor_id = c.colid
AND cd.name = 'MS_Description'
WHERE t.type = 'u'
ORDER BY t.name ,
c.colorder

You can join multiple system views to come up with your desired output. I’m sure there are other views you can find inside SQL Server that will show you what you want to see. The query above will give you something like this:

From the sample data above, you notice that there are no data in the description attributes for the tables and columns. This is typical to commercial vendor applications. Thorough documentation is a necessity we cannot afford to take for granted. And, yet, most people consider documentation as the least important thing in their project.

After you brewed your t-sql scripts, there are additional things you must do to come up with a usable documentation.

SQL Doc

Now here comes SQL Doc from Red Gate. It is a tool that automatically generates documentation based on your database schema. Compared to the process I described above, SQL Doc generates a thorough documentation of your databases faster than you ever could. See all those NULL on the table_desc and column_desc columns? You can fill them up with intuitive data at runtime and SQL Doc will write them back to the extended properties of your database objects.

Benefits of SQL Doc

Red Gate identifies the SQL Doc feautures as the following:

• Save time by automatically generating documentation
• Eliminate tedious and time-consuming manual documentation tasks
• Satisfy audit requirements by keeping complete documentation
• Document a database in a couple of clicks, from within SSMS
• Keep teams up to date by distributing documentation

You definitely save time, effort, and resources by using SQL Doc to generate your documentation. Instead of getting consumed in an endless loop of coding, tweaking, and compiling your documentation, you can focus more on other stuff that truly matters without compromising your metadata.

Documentation Format Options

You can generate three types of document format with SQL Doc; namely, Word document (.doc), Web page (.html), and Help file (.chm). I’d prefer HTML mostly for its convenience. Or, if you are developing a stand-alone application, you’d want it in a help file that is integrated into the app as an additional feature.