Get Hard Drive Space SQL (SIMPLE)

I was looking around online for solutions to get Hard drive size and found it seemed to be a pretty hot topic and had many complicated solutions. Most of which either required building a separate .vbs script to be called in sql or utilizing a WMI connection with WQL query. Although it was interesting reading about this stuff I did not like how complicated it was. A very smart man once said "Do everything as simple as possible but no simpler" Eventually I found a simple solution..

"fsutil"

A CMD Shell function which spits out drive size in bytes. So I was able dump the results into a temp to isolate the size of the drive with a little charindex, little math and shazam! ...Still cant believe there is no built in SQL Function for this. SQL Server 2010? :p

TSQL BELOW...

DECLARE

@cmd VARCHAR(8000)

,@STRING VARCHAR(MAX)

,@INTSTARTINDEX int

,@INTENDINDEX int

,@SUBSTRING VARCHAR(50)

,@Size BIGINT

,@Disk VARCHAR(50)

SET @Disk = 'H' --Set Drive Letter.

CREATE TABLE #Line

(Line VARCHAR(8000))

SET @cmd = 'fsutil volume diskfree ' + @Disk + ':'

INSERT INTO #Line

EXEC MASTER..Xp_cmdshell @cmd

SET @STRING = (SELECT * FROM #Line WHERE line LIKE '%Total # of bytes%')

SET @INTSTARTINDEX = Charindex('bytes', @STRING) + 20 -- THE NUMBER OF CHARACTERS IN THE WORD 'FILE(S)'

SET @INTENDINDEX = Charindex(':', @STRING) + 20

SET @SUBSTRING = Rtrim(Ltrim(Substring(@STRING, @INTSTARTINDEX, @INTENDINDEX - @INTSTARTINDEX)))

SET @Size = @SUBSTRING

SELECT @Disk as 'Drive', @Size/1073741824 as 'Size GB'

DROP TABLE #Line