DECLARE @sqlver sql_variant

DECLARE @sqlver2 varchar(20)

DECLARE @sqlver3 int

SELECT @sqlver = SERVERPROPERTY('productversion')

SELECT @sqlver2 = CAST(@sqlver AS varchar(20))

select @sqlver3 = SUBSTRING(@sqlver2,1,1)

-- 1 = 2008 8 = 2000 and 9 = 2005 1 is short for 10

BEGIN

--select @sqlver3 only uncomment to see state of version

IF @sqlver3 = 1 GOTO SERVER2008

IF @sqlver3 = 9 GOTO SERVER2000

IF @sqlver3 = 8 GOTO SERVER2000

GOTO THEEND

END

SERVER2008:

declare @svrName varchar(255)

declare @sql varchar(400)

--by default it will take the current server name, we can the set the server name as well

set @svrName = @@SERVERNAME

set @sql = 'powershell.exe -c "Get-WmiObject -Class Win32_Volume -Filter ''DriveType = 3'' | select name,capacity,freespace | foreach{$_.name+''|''+$_.capacity/1048576+''%''+$_.freespace/1048576+''*''}"'

--creating a temporary table

CREATE TABLE #output

(line varchar(255))

--inserting disk name, total space and free space value in to temporary table

insert #output

EXEC xp_cmdshell @sql

--script to retrieve the values in GB from PS Script output

select rtrim(ltrim(SUBSTRING(line,1,CHARINDEX('|',line) -1))) as drive

,round(cast(rtrim(ltrim(SUBSTRING(line,CHARINDEX('%',line)+1,

(CHARINDEX('*',line) -1)-CHARINDEX('%',line)) )) as Float) /1024 ,0)as 'freespace'

,round(cast(rtrim(ltrim(SUBSTRING(line,CHARINDEX('|',line)+1,

(CHARINDEX('%',line) -1)-CHARINDEX('|',line)) )) as Float)/1024,0) as 'totalspace'

,((round(cast(rtrim(ltrim(SUBSTRING(line,CHARINDEX('%',line)+1,

(CHARINDEX('*',line) -1)-CHARINDEX('%',line)) )) as Float) /1024 ,0)) / (round(cast(rtrim(ltrim(SUBSTRING(line,CHARINDEX('|',line)+1,

(CHARINDEX('%',line) -1)-CHARINDEX('|',line)) )) as Float)/1024,0)) * 100) as percentfree

from #output

where line like '[A-Z][:]%'

--and ((round(cast(rtrim(ltrim(SUBSTRING(line,CHARINDEX('%',line)+1,

-- (CHARINDEX('*',line) -1)-CHARINDEX('%',line)) )) as Float) /1024 ,0)) / (round(cast(rtrim(ltrim(SUBSTRING(line,CHARINDEX('|',line)+1,

--(CHARINDEX('%',line) -1)-CHARINDEX('|',line)) )) as Float)/1024,0)) * 100) < 5

order by drive

--script to drop the temporary table

drop table #output

GOTO THEEND

SERVER2000:

SET NOCOUNT ON;

DECLARE @v_cmd nvarchar(255)

,@v_drive char(99)

,@v_sql nvarchar(255)

,@i int

SELECT @v_cmd = 'fsutil volume diskfree %d%'

SET @i = 1

CREATE TABLE #drives(iddrive smallint ,drive char(99))

CREATE TABLE #t(drive char(99),shellCmd nvarchar(500));

CREATE TABLE #total(drive char(99),freespace decimal(9,2), totalspace decimal(9,2));

-- Use mountvol command to

INSERT #drives (drive)

EXEC master..xp_cmdshell 'mountvol'

DELETE #drives WHERE drive not like '%:\%' or drive is null

WHILE (@i <= (SELECT count(drive) FROM #drives))

BEGIN

UPDATE #drives

SET iddrive=@i

WHERE drive = (SELECT TOP 1 drive FROM #drives WHERE iddrive IS NULL)

SELECT @v_sql = REPLACE(@v_cmd,'%d%',LTRIM(RTRIM(drive))) from #drives where iddrive=@i

INSERT #t(shellCmd)

EXEC master..xp_cmdshell @v_sql

UPDATE #t

SET #t.drive = d.drive

FROM #drives d

WHERE #t.drive IS NULL and iddrive=@i

SET @i = @i + 1

END

INSERT INTO #total

SELECT bb.drive

,CAST(CAST(REPLACE(REPLACE(SUBSTRING(shellCmd,CHARINDEX(':',shellCmd)+1,LEN(shellCmd)),SPACE(1),SPACE(0))

,char(13),SPACE(0)) AS NUMERIC(32,2))/1024/1024/1024 AS DECIMAL(9,2)) as freespace

,tt.titi as total

FROM #t bb

JOIN (SELECT drive

,CAST(CAST(REPLACE(REPLACE(SUBSTRING(shellCmd,CHARINDEX(':',shellCmd)+1,LEN(shellCmd)),SPACE(1),SPACE(0))

,char(13),SPACE(0)) AS NUMERIC(32,2))/1024/1024/1024 AS DECIMAL(9,2)) as titi

FROM #t

WHERE drive IS NOT NULL

AND shellCmd NOT LIKE '%free bytes%') tt

ON bb.drive = tt.drive

WHERE bb.drive IS NOT NULL

AND bb.shellCmd NOT LIKE '%avail free bytes%'

AND bb.shellCmd LIKE '%free bytes%';

-- SET FreespaceTimestamp = (GETDATE())

SELECT RTRIM(LTRIM(drive)) as drive

,freespace

,totalspace

,CAST((freespace/totalspace * 100) AS DECIMAL(5,2)) as [percent free]

FROM #total

--WHERE (freespace/totalspace * 100) < 5

ORDER BY drive

DROP TABLE #drives

DROP TABLE #t

DROP TABLE #total

THEEND: