Check Drive Space with T-SQL and PowerShell

sp_configure ‘show advanced options’, 1
go
Reconfigure with override
go
–Turn on xp_cmdshell
sp_configure ‘xp_cmdshell’, 1
go

Reconfigure with override
go
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

Declare @svrName varchar(255)
Declare @sql varchar(400)

–by default it will take the current server name, you can the set the server name as well
SET @svrName = @@SERVERNAME
SET @sql = ‘powershell.exe -c “Get-WmiObject -ComputerName ‘ + QUOTENAME(@svrName,””) +
‘ -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

CREATE Table #DriveSpace
(ServerName sysname,
[PhysicalName] sysname,
[Capacity(GB)] nvarchar(20),
[Drive FreeSpace(GB)] nvarchar(20))

INSERT INTO #DriveSpace
([ServerName]
,[PhysicalName]
,[Capacity(GB)]
,[Drive FreeSpace(GB)])
SELECT @@SERVERNAME
,rtrim(ltrim(SUBSTRING(line,1,CHARINDEX(‘|’,line) -1))) as PhysicalName
,round(cast(rtrim(ltrim(SUBSTRING(line,CHARINDEX(‘|’,line)+1,
(CHARINDEX(‘%’,line) -1)-CHARINDEX(‘|’,line)) )) as Float)/1024,0) as ‘capacity(GB)’
,round(cast(rtrim(ltrim(SUBSTRING(line,CHARINDEX(‘%’,line)+1,
(CHARINDEX(‘*’,line) -1)-CHARINDEX(‘%’,line)) )) as Float) /1024 ,0)as ‘Drive freespace(GB)’
FROM #output
WHERE line like ‘[A-Z][:]%’
order by PhysicalName
— Get Results
SELECT * from #DriveSpace
go
–Turn off xp_cmdshell
sp_configure ‘show advanced options’, 0
go
Reconfigure with override

–Drop temp tables
DROP TABLE #output

DROP TABLE #DriveSpace
go