/*

Author : Vidhya Sagar

Date : 01st Feb 2012

Written for sql-articles.com

Version 3

*/

DECLARE @VBSPATH VARCHAR(200)

SET @VBSPATH='cscript D:\spacecheck.vbs' -- Change the path here

SET NOCOUNT ON

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

DROP TABLE ##tmp

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

INSERT ##tmp

EXEC master.dbo.xp_cmdshell @VBSPATH

SET ROWCOUNT 3

DELETE ##tmp

SET ROWCOUNT 0

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

DROP TABLE ##tmp2

CREATE TABLE ##tmp2(DriveName VARCHAR(100),DriveDescription VARCHAR(15),TotalDiskSpace_in_MB VARCHAR(10), Freespace_in_MB VARCHAR(10))

INSERT ##tmp2

SELECT RTRIM(SUBSTRING(diskspace,31,100)), SUBSTRING(diskspace,18,13), SUBSTRING(diskspace,1,8) ,SUBSTRING(diskspace,9,9) FROM ##tmp WHERE diskspace IS NOT NULL

SELECT * FROM ##tmp2

/* Uncomment to show the output in GB instead of MB

SELECT DriveName,DriveDescription,

CONVERT(DECIMAL,TotalDiskSpace_in_MB)/1024 TotalDiskSpace_in_GB

,CONVERT(DECIMAL,Freespace_in_MB)/1024 Freespace_in_GB FROM ##tmp2

*/