Total size of sql databases

26 Mar 2009 6:35 PM

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

26 Mar 2009 10:08 PM

Richard Mueller [MVP]

Dude wrote:

Show quoteHide quote

>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
--