Check disk Space though SQL Server

I had used WMI script to do this, Copy the vbs script and save it to a location as disksp.vbs and use this location in the sql script to get the result.

VBS Script


Const HARD_DISK = 3
strComputer = "."
Set objWMIService = GetObject("winmgmts:" _
    & "{impersonationLevel=impersonate}!\\" & strComputer & "\root\cimv2")


Set colDisks = objWMIService.ExecQuery _
    ("Select * from Win32_LogicalDisk Where DriveType = " & HARD_DISK & "")


For Each objDisk in colDisks
Wscript.Echo objDisk.DeviceID & " " & mid((objDisk.size)/1048576,1,10) & " " & mid((objDisk.Freespace)/1048576,1,10)
Next

SQL Script

SET NOCOUNT ON

IF EXISTS (SELECT 1 FROM tempdb..sysobjects WHERE NAME ='##Temp')

DROP TABLE ##Temp

CREATE TABLE ##Temp(diskspace VARCHAR(200))

INSERT ##Temp

EXEC master.dbo.xp_cmdshell 'cscript C:\disksp.vbs' 

SET ROWCOUNT 3

DELETE ##Temp

SET ROWCOUNT 0

IF EXISTS (SELECT 1 FROM tempdb..sysobjects WHERE NAME ='##Temp2')

DROP TABLE ##Temp2

CREATE TABLE ##Temp2(Driveletter VARCHAR(12),TotalDiskSpace_in_MB DECIMAL(18,4), Freespace_in_MB DECIMAL(18,4))

INSERT INTO ##Temp2 SELECT SUBSTRING(diskspace,1,3) , CONVERT(Decimal,SUBSTRING(diskspace,4,10)),

CONVERT(Decimal,SUBSTRING(diskspace,15,10)) FROM ##Temp WHERE diskspace IS NOT NULL

SELECT * FROM ##Temp2

Driveletter TotalDiskSpace_in_MB Freespace_in_MB

C:                         39998.0000                                 6526.0000

D:                         55004.0000                                 26942.0000

E:                         57624.0000                                 26569.0000