'Declare Variables for Server name, Database, DB File, Log File etc
Dim vServerName
Dim objServer
Dim objDatabase
Dim objDBFile
Dim objLogFile
Dim xlApp
Dim xlBook
Dim xlSheet
Dim Row
Dim Cell
 
' Messagebox for server name
vServerName = InputBox("Please Enter Server Name:","Server Name","(local)")
If vServerName = "" Then
vServerName = "(local)"
End If
 
'Create Excel Object
Set xlApp = CreateObject("Excel.Application")
 
'Add Workbook to Excel Obj
Set xlBook = xlApp.Workbooks.Add
 
'Add Sheet to WorkBook
Set xlSheet = xlBook.Worksheets.Add
 
'Make Visibility for Excel App
xlSheet.Application.Visible = True
 
' Set column headers
xlSheet.Cells(1, 1) = "Database Name"
xlSheet.Cells(1, 2) = "Space used (MB)"
xlSheet.Cells(1, 3) = "Space Available (MB)"
xlSheet.Cells(1, 4) = "DBFile (MB)"
xlSheet.Cells(1, 5) = "LogFile (MB)"
Row = 2
 
xlSheet.Rows("1:1").Font.Bold = True
xlSheet.Range("B:E").HorizontalAlignment = -4152
 
' Create the server object using SQL-DMO
Set objServer = CreateObject("SQLDMO.SQLServer2")
 
'Set Authentication for Server
' Login with current Windows account
objServer.LoginSecure = True
objServer.Connect vServerName
 
 
'Loop through all the databases and get the properties of databases
For each objDatabase in objServer.Databases
 
SET objDBFile = objDatabase.Filegroups("PRIMARY").DBFiles(1)
SET objLogFile = objDatabase.TransactionLog.LogFiles(1)
xlSheet.Cells(Row, 1).Value = objDatabase.Name
xlSheet.Cells(Row, 2).Value = objDatabase.Size
xlSheet.Cells(Row, 3).Value = Round(objDatabase.SpaceAvailableInMB,3)
xlSheet.Cells(Row, 4).Value = objDBFile.size
xlSheet.Cells(Row, 5).Value = objLogFile.size
 
Row = Row + 1
Set objDBFile = Nothing
Set objLogFile = Nothing
Next
 
xlSheet.Cells.EntireColumn.AutoFit
 
 
'Save our changes
xlBook.SaveAs ("C:\DBSpaceUsage.xls")
xlBook.Close
 
MsgBox "Check the file at C:\DBSpaceUsage.xls"
 
' Clean up memory
Set xlSheet = Nothing
Set xlBook = Nothing
Set xlApp = Nothing
set objDatabase = Nothing
Set objServer = Nothing