by Rupert Walsh - GUI Computing
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.