Find Db space used  using SQL-DMO

,

Use SQL-DMO to find database size used for more than one sql server in your enviroment.

'Find the DataFile usage for each database, for each SQL server in the text file
'Place name of SQLservers in the text file in Sqlservers.txt in c drive.
'Created by Ravi Pannem, Sprint
'=============================================================================
Dim oServer
Set FSO = CreateObject("Scripting.FileSystemObject")
Set SQLSrvrs = FSO.OpenTextFile("C:\SqlServers.txt")


If FSO.FileExists("C:\SQLServers_Info.txt") then
	FSO.DeleteFile "C:\SQLServers_Info.txt", True
End If
	
Set OutPutFile=FSO.OpenTextFile("C:\SQLServers_Info.txt",8,True)
outputfile.writeline "Name of database"&","&"DataSpace Usage (in MB)"&vbCrLf&"======================================"


dim arraylines()

do until SQLSrvrs.atendofstream = True
	redim arraylines(i)
	arraylines(i) = SQLsrvrs.readline
	strComputer=arraylines(i)
	call Find_Spaceused()
loop

Sub Find_Spaceused()

Set oServer=CreateObject("SQLDMO.Sqlserver")
oServer.LoginSecure=True
oServer.Connect(strComputer)

set oDatabases=oServer.Databases
	outputfile.writeline oServer.NetName&vbCrlf&"========================================"
	for each db in oDatabases
		outputfile.writeline db.name&","&db.dataspaceusage/1000
	next
	outputfile.writeline "========================================" 
oServer.Disconnect

set oServer=Nothing

End Sub

msgbox "DONE"

Rate

Share

Share

Rate