This will give you hard drive spaces ect:
use master

SET NOCOUNT ON
DECLARE @hr int
DECLARE @fso int
DECLARE @drive char(1)
DECLARE @odrive int
DECLARE @TotalSize varchar(20)
DECLARE @MB bigint ; SET @MB = 1048576

CREATE TABLE #drives (ServerName varchar(15), drive char(1) PRIMARY KEY,
FreeSpace int NULL, TotalSize int NULL, FreespaceTimestamp DATETIME
NULL) INSERT #drives(drive,FreeSpace) EXEC master.dbo.xp_fixeddrives
EXEC @hr=sp_OACreate 'Scripting.FileSystemObject',@fso OUT IF @hr <> 0
EXEC sp_OAGetErrorInfo @fso DECLARE dcur CURSOR LOCAL FAST_FORWARD FOR
SELECT drive from #drives ORDER by drive OPEN dcur FETCH NEXT FROM dcur
INTO @drive WHILE @@FETCH_STATUS=0 BEGIN EXEC @hr = sp_OAMethod
@fso,'GetDrive', @odrive OUT, @drive IF @hr <> 0 EXEC sp_OAGetErrorInfo
@fso EXEC @hr = sp_OAGetProperty @odrive,'TotalSize', @TotalSize OUT IF
@hr <> 0 EXEC sp_OAGetErrorInfo @odrive UPDATE #drives SET
TotalSize=@TotalSize/@MB, ServerName = @@SERVERNAME, FreespaceTimestamp
= (GETDATE()) WHERE drive=@drive FETCH NEXT FROM dcur INTO @drive END
CLOSE dcur DEALLOCATE dcur EXEC @hr=sp_OADestroy @fso IF @hr <> 0 EXEC
sp_OAGetErrorInfo @fso SELECT ServerName, drive, TotalSize as
'Total(MB)', FreeSpace as 'Free(MB)',
CAST((FreeSpace/(TotalSize*1.0))*100.0 as int) as 'Free(%)',
FreespaceTimestamp FROM #drives ORDER BY drive DROP TABLE #drives RETURN
GO

Tad Huck
{Database Czar - " If you can't learn to do something well, learn to
enjoy doing it poorly. "}

REAL PAGE INC.
4000 International Parkway
Carrollton, Texas 75007-1913
(972) 820-3432.



This message is intended only for the use of the individual(s) or entity to which it is addressed and may contain information that is privileged, confidential, and/or proprietary to RealPage and its affiliated companies. If the reader of this message is not the intended recipient, you are hereby notified that any dissemination, distribution, forwarding or copying of this communication is prohibited without the express permission of the sender. If you have received this communication in error, please notify the sender immediately and delete the original message.

MS Sql Server LazyDBA home page