Image of Navigational Map linked to Home / Contents / Search Documenting SQL Server

by Rupert Walsh - GUI Computing
Image of Line Break

Most programmers dislike writing system documentation. We'd rather be developing applications than performing tedious tasks like typing out every field of every table. This becomes especially annoying when table definitions change during development. I know they're not supposed to, but sometimes they do, and often for reasons beyond your control.

Access has a database documenter found, for reasons not known to me, under the Tools|Analyze|Documenter menu. It produces a halfway decent report, with a few options. But there's one huge problem with this: you probably use Microsoft Word to create all your other documentation. I know you can save that report as RTF and get to Word that way, but MS SQL server doesn't have an in-built tool at all, although I'm sure plenty of people out there have written them.

When I document a database, I generally follow a pretty simple and standard format. Table name, field names, types, lengths and whether they are required. I put all this information into tables in a Word document.

Those of you who read Matt Gray's article on using Word from VB in the last issue would already know that we can drive Word using the Microsoft Word 8.0 Object Library. Of course that's only half the story. We also want to automatically get the table information out of our database. For an Access database, you could use DAO and TableDef objects. However, because I'm working with SQL Server at the moment, I'm going to show you how to do it with the MS SQL system tables. It also gives those of you not familiar with them some insight into the inner workings of MS SQL. Note that you don't normally see these tables when browsing a database with the Enterprise Manager.

All the table and field information we need is included in three tables: sysOjects, sysColumns and sysTypes.

To keep things simple, I've put almost all the code under the click event of one button, and hard coded the database connection and document file name. My code does basically the following:

Here's the result:

To make the document look nice, I use Word 'styles' for the titles:

'Add a heading for the document
Selection.Style = ActiveDocument.Styles("Heading 1")
Selection.TypeText Text:="Table Definitions"
Selection.TypeParagraph

The code reads the table names, and object ID's from the sysObjects table. The rsColumns recordset is opened freshly for each table record, joining on the object ID:

'Get all sys objects of type U (user tables)
sSql = "SELECT Name, ID FROM sysObjects WHERE sysObjects.Type = 'U'"
rsTables.Open sSql, connDB, adOpenForwardOnly, adLockReadOnly

Do While Not rsTables.EOF
	lblStatus.Caption = "Processing table " & rsTables("Name")
	DoEvents

	' Get field names, types, lengths and Null requirements for current table
	sSql = "SELECT sysColumns.Name AS ColumnName, " & _
		"sysTypes.name AS DataType, " & _
		"sysColumns.Length AS Length, " & _
		"sysColumns.Status AS Status " & _
		"FROM sysColumns, sysTypes " & _
		"WHERE sysColumns.id = " & rsTables("id") & _
		"AND sysColumns.usertype = sysTypes.usertype"

	rsColumns.Open sSql, connDB, adOpenForwardOnly, adLockReadOnly
	...

I add a title row for each table and field's properties by looping through rsColumns. Note that I separate the columns with tab characters:

'Get start pos for this table
nStartPos = Selection.Start

'Do bold titles for table
Selection.Style = ActiveDocument.Styles("Normal")
Selection.Font.Bold = wdToggle
Selection.TypeText Text:="Field Name" & vbTab & "Data Type" & vbTab & "Size" & vbTab & "Required" & vbCrLf
Selection.Font.Bold = wdToggle

'Reset table text
sTable = ""

'Add each Row to the table
Do While Not rsColumns.EOF
	sTable = sTable & rsColumns("ColumnName") & vbTab & _
		rsColumns("DataType") & vbTab & _
		rsColumns("Length") & vbTab & _
		ConvertStatus(rsColumns("Status")) & vbCrLf
	rsColumns.MoveNext
Loop
rsColumns.Close
'Add table text to document Selection.TypeText Text:=sTable 'Get end position nEndPos = Selection.Start ...

I next select the entire table text, and use the ConvertToTable function to do the work:

'Select the table text
Selection.SetRange nStartPos, nEndPos

'Convert to a table
Selection.ConvertToTable Separator:=wdSeparateByTabs, NumColumns:=4, _
	Format:=wdTableFormatNone, ApplyBorders:=True, ApplyShading _
	:=True, ApplyFont:=True, ApplyColor:=True, ApplyHeadingRows:=True, _
	ApplyLastRow:=False, ApplyFirstColumn:=True, ApplyLastColumn:=False, _
	AutoFit:=False

'Go to the end of the document
Selection.EndKey Unit:=wdStory

rsTables.MoveNext

Finally, I save the document:

'Save document - hard coded at the moment
oDoc.SaveAs App.Path & "\DBDEF.DOC"

The only other function I've written is used to return "True" or "False" depending on a column's status property.

Private Function ConvertStatus(ByVal nStatus As Variant) As String
Dim sHexStatus As String
ConvertStatus = "Yes"

'Status uses a hex bitmapping
If Not IsNull(nStatus) Then
	sHexStatus = Hex$(nStatus)
	If Right(sHexStatus, 1) = "8" Then
		ConvertStatus = "No"
	End If

End If
End Function

The complete source is available for download.

Obvious extensions to this utility are to make the formatting a bit fancier, or to get rid of the hard coding.

There you have it - I've successfully turned a documentation task in to a development task! And you thought it couldn't be done.



Written by: Rupert Walsh
August '98

Image of Arrow linked to Previous Article Image of Arrow linked to Next Article
Image of Line Break
[HOME] [TABLE OF CONTENTS] [SEARCH]