LISTING 1: Enumerate a Server’s Databases

Sub EnumerateDatabaseSpecs()
On Error GoTo Specs_Trap
Dim srv1 As SQLDMO.SQLServer
Dim obj1 As Object

'Instantiate server object and
'connect it.
Set srv1 = New SQLDMO.SQLServer
srv1.Connect "Cabxli", "sa", ""

'Compute to accommodate the longest name.
mwd = 5
Set obj1 = srv1.Databases
For Each dbs1 In obj1
DoLengthTest:
    If mwd <= Len(dbs1.Name) Then Err.Raise 5
Next dbs1

'Print row of column headers.
Debug.Print "Database Name" & _
    String(mwd - 13, " ") & _
    "Date Created", "Total Tables", "Total Views", _
    "Total Stored Procs"

'Print specs for each database in a separate row.
For Each dbs1 In obj1
    Debug.Print dbs1.Name & _
        String(mwd - Len(dbs1.Name), " ") & _
        Left(dbs1.CreateDate, 10), dbs1.Tables.Count, _
        dbs1.Views.Count, dbs1.StoredProcedures.Count
Next dbs1

Specs_Exit:
Set obj1 = Nothing
srv1.DisConnect
Set srv1 = Nothing
Exit Sub

Specs_Trap:
If Err.Number = 5 Then
    mwd = mwd + 5
    Resume DoLengthTest
Else
    MsgBox "Unanticipated error occurred."
End If

End Sub