Total size of sql databases

03-26-2009

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

03-26-2009

Dude wrote:

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

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Total Database Size	Mark Gibbons	Windows Update	0	11-24-2007 05:07 AM
Total file size.	auser	Windows Vista General Discussion	5	08-31-2007 06:00 PM
Total File Size In WMP11?	Andy	Windows Media Player	2	04-19-2007 08:20 PM
RE: View total disk size	urkec	Scripting	0	12-02-2006 08:32 AM
No easy way to see folder's total size.	Radzi	Windows Vista General Discussion	5	09-13-2006 06:13 AM