Auto Generating a Data Dictionary – Part 2

This slab takes the data gathered in Part 1 and turns it into an Excel workbook. Very useful for most needs!

1. Start a new Winforms project.
2. Make it look like this:

The Data Dictionary Generator UI

3. Add these references:

• Microsoft.SqlServer.Smo
• Microsoft.SqlServer.Management.Sdk.Sfc
• Microsoft.SqlServer.ConnectionInfo
• Microsoft.Office.Interop.Excel
• and of course, DataLayer

4. Paste this code into Form1’s code behind:

Imports Microsoft.SqlServer.Management.Smo
Imports Microsoft.SqlServer.Management.Common
Imports Microsoft.Office.Interop
Imports System.Data
Imports DataLayer

Public Class Form1
    Private DataSource As String = "Data Source="
    Private InitialCatalog As String = ";Initial Catalog="
    Private IntegratedSecurity As String = ";Integrated Security=True"

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Me.Show()
        Me.Cursor = Cursors.WaitCursor
        Dim servers As DataTable
        servers = SmoApplication.EnumAvailableSqlServers(False)
        For Each drServer As DataRow In servers.Rows
            ComboBox1.Items.Add(drServer("Name"))
        Next
        ComboBox1.Text = "<Select a server...>"
        TextBox1.Text = My.Computer.FileSystem.SpecialDirectories.MyDocuments
        TextBox2.Text = DataSource & InitialCatalog & IntegratedSecurity
        Me.Cursor = Cursors.Default
    End Sub

    Private Sub ComboBox1_Leave(ByVal sender As System.Object, ByVal e As System.EventArgs) _
    Handles ComboBox1.Leave, _
            ComboBox1.SelectedIndexChanged

        GetDatabases()
    End Sub

    Private Sub ComboBox2_Leave(ByVal sender As System.Object, ByVal e As System.EventArgs) _
    Handles ComboBox2.Leave, _
            ComboBox2.SelectedIndexChanged

        TextBox2.Text = DataSource & ComboBox1.Text & InitialCatalog & ComboBox2.Text & IntegratedSecurity

        TextBox1.Text = My.Computer.FileSystem.SpecialDirectories.MyDocuments _
            & "\" & ComboBox1.Text & "-" & ComboBox2.Text & "-DataDictionary.xls"
    End Sub

    Private Sub GetDatabases()
        If ComboBox1.Text <> "" And ComboBox1.Text <> "<Select a server...>" Then
            Dim theServerInfo As New Server(New ServerConnection(ComboBox1.Text))
            For Each currentDatabase As Database In theServerInfo.Databases
                Me.ComboBox2.Items.Add(currentDatabase.Name)
            Next
        End If

        TextBox2.Text = DataSource & ComboBox1.Text & InitialCatalog & IntegratedSecurity
    End Sub

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim SaveFileDialog As New SaveFileDialog
        SaveFileDialog.FileName = ComboBox1.Text & "-" & ComboBox2.Text & "-DataDictionary.xls"
        SaveFileDialog.InitialDirectory = My.Computer.FileSystem.SpecialDirectories.MyDocuments
        SaveFileDialog.Filter = "Excel Workbooks (*.xls)|*.xls|All Files (*.*)|*.*"

        If (SaveFileDialog.ShowDialog(Me) <> System.Windows.Forms.DialogResult.OK) Then
            'user canceled out
            Exit Sub
        End If

        TextBox1.Text = SaveFileDialog.FileName
    End Sub

    Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
        Dim iRowCount As Integer, iColCount As Integer
        Dim oExcel As Object
        Dim oBook As Object
        Dim oSheet1 As Object, oSheet2 As Object, oSheet3 As Object
        oExcel = CreateObject("Excel.Application")
        oBook = oExcel.Workbooks.Add
        Dim MyDAL As New DAL(TextBox2.Text)
        Dim Datasets As Generic.SortedList(Of Integer, DataTable) = MyDAL.GetMultipleDataTables("EXEC dbo.GenerateDataDictionary")
        If Datasets Is Nothing Then
            MsgBox("Unable to gather results.  Is the stored procedure installed?", MsgBoxStyle.Critical + MsgBoxStyle.OkOnly, "Error")
            Exit Sub
        End If

        oSheet1 = oBook.Worksheets(1)
        With oSheet1
            .Name = "Tables"
            .Range("A1").Value = "Table Name"
            .Range("B1").Value = "Description"
            .Range("A1:B1").Font.Bold = True
            iRowCount = Datasets(0).Rows.Count
            iColCount = Datasets(0).Columns.Count
            .Range("A2").Resize(iRowCount, iColCount).Value = GetDataAsObject(iRowCount, iColCount, Datasets(0))
            .Range("A1:B" & iRowCount.ToString).EntireColumn.Columns.AutoFit()
            .Range("A2").Select()
            .Application.ActiveWindow.FreezePanes = True
        End With

        oSheet2 = oBook.Worksheets(2)
        With oSheet2
            .Name = "Columns"
            .Range("A1").Value = "Column"
            .Range("B1").Value = "Table"
            .Range("C1").Value = "Data Type"
            .Range("D1").Value = "Default Value"
            .Range("E1").Value = "Nullable"
            .Range("F1").Value = "Description"
            .Range("A1:F1").Font.Bold = True
            iRowCount = Datasets(1).Rows.Count
            iColCount = Datasets(1).Columns.Count
            .Range("A2").Resize(iRowCount, iColCount).Value = GetDataAsObject(iRowCount, iColCount, Datasets(1))
            .Range("A1:F" & iRowCount.ToString).EntireColumn.Columns.AutoFit()
            .Activate()
            .Range("A2").Select()
            .Application.ActiveWindow.FreezePanes = True
        End With

        oSheet3 = oBook.Worksheets(3)
        With oSheet3
            .Name = "Relationships"
            .Range("A1").Value = "Constraint"
            .Range("B1").Value = "Type"
            .Range("C1").Value = "Table"
            .Range("D1").Value = "Column"
            .Range("E1").Value = "Constrained By"
            .Range("F1").Value = "Description"
            .Range("G1").Value = "Delete Action"
            .Range("H1").Value = "Update Action"
            .Range("A1:H1").Font.Bold = True
            iRowCount = Datasets(2).Rows.Count
            iColCount = Datasets(2).Columns.Count
            .Range("A2").Resize(iRowCount, iColCount).Value = GetDataAsObject(iRowCount, iColCount, Datasets(2))
            .Range("A1:H" & iRowCount.ToString).EntireColumn.Columns.AutoFit()
            .Activate()
            .Range("A2").Select()
            .Application.ActiveWindow.FreezePanes = True
        End With

        oSheet1.Activate()

        Try
            oBook.SaveAs(TextBox1.Text)
        Catch ex As Exception
            If MsgBox("There was a problem saving the file." & Environment.NewLine _
                      & "Do you want to see the error trace?", _
                      MsgBoxStyle.Question + MsgBoxStyle.YesNo, "Error") = MsgBoxResult.Yes Then

                MsgBox(ex.ToString, MsgBoxStyle.Information + MsgBoxStyle.OkOnly, "Error")
            End If
        End Try

        'kill the objects
        oSheet1 = Nothing
        oSheet2 = Nothing
        oSheet3 = Nothing
        oBook = Nothing
        oExcel.Quit()
        oExcel = Nothing
        GC.Collect()

        MsgBox("Data Dictionary created.", MsgBoxStyle.Information + MsgBoxStyle.OkOnly, "Notice")
    End Sub

    Private Shared Function GetDataAsObject(ByVal iRowCount As Integer, ByVal iColCount As Integer, ByVal dt As DataTable) As Object(,)
        Dim oData(iRowCount, iColCount) As Object, iRow As Integer, iCol As Integer

        For iRow = 0 To iRowCount - 1
            For iCol = 0 To iColCount - 1
                oData(iRow, iCol) = dt.Rows(iRow).Item(iCol)
            Next
        Next
        Return oData
    End Function

    Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click
        Me.Close()
    End Sub
End Class