Total size of sql databases

Richard Mueller [MVP]

2009-03-26 22:08:32 UTC

Post by Dude
I have a script that lists all the databases on a server, I would like for
it to also report the size of each database. Anyone know how I can add that
into this script?
strInitialDB = "master" 'default database
strSQLServer = "server name" 'type the name of SQL Server instance here.
strQuery = "SELECT [name], [dbid], [crdate], [filename] FROM
[master].[dbo].[sysdatabases]"
Set objConn = CreateObject("ADODB.Connection")
' open the master data source
objConn.Open "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist
Security Info=False;Initial Catalog=" & strInitialDB & ";Data Source=" &
strSQLServer
'set the stored procedure to execute
set objRS = objConn.execute(strQuery)
if objRS.recordCount = 0 then
wscript.echo "No records were found."
wscript.quit
end if
strHTML = "<body>ALL SQL SERVER DATABASES ON SERVER:" & strSQLServer
strHTML = strHTML & "<table><tr bgcolor = #6699cc>"
strHTML = strHTML & "<td>DATABASE NAME<td>DATABASE ID<td>DATE
CREATED<td>DATABASE FILE PATH"
strHTML = strHTML & "</tr>"
while not objRS.EOF
strHTML = strHTML & "<tr bgcolor = #ffe4c4>"
for i = 0 to objRS.fields.Count -1
strHTML = strHTML & "<td>" & objRS.fields(i).value
next
strHTML = strHTML & "</tr>"
objRS.moveNext
wend
strHTML = strHTML & "</body></table>"
Set objIE = CreateObject("InternetExplorer.Application")
objIE.Navigate("about:blank")
objIE.ToolBar = 0
objIE.StatusBar = 0
objIE.width = 800
Set objDoc = objIE.Document.Body
objIE.Visible = True
objIE.Document.Body.innerHTML=strHTML
objRS.close
objConn.close

The value if the filename field is the name and path of the *.mdf file. One
option would be to use the FileSystemObject to bind to the File object and
retrieve the Size property (in bytes). But I don't think that's allowed in
IE. In brief, something similar to:

Set objFSO = CreateObject("Scripting.FileSystemObject")
Do Until objRS.EOF
strName = objRS.Fields("name").Value
strFile = objRS.Fields("filename").Value
Set objFile = objFSO.GetFile(strFile)
strSize = objFile.Size
' Display file size in KB.
Wscript.Echo strName & " (" & FormatNumber((strSize / 1024), 0) & " KB)"
objRS.MoveNext
Loop

--
Richard Mueller
MVP Directory Services
Hilltop Lab - http://www.rlmueller.net
--