Reference: http://www.mssqltips.com/tip.asp? Tip = 2444
Disk size and available space
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 = case charindex ('\', @ servername) When 0 then @ servername else
Left (@ servername, charindex ('\', @ servername)-1) End
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
Print (@ SQL)
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 MB from PS Script output
Select rtrim (ltrim (substring (line, 1, charindex ('|', line)-1) as drivename
, Round (cast (rtrim (ltrim (substring (line, charindex ('|', line) + 1,
(Charindex ('%', line)-1)-charindex ('|', line) as float), 0) as 'capacity (MB )'
, Round (cast (rtrim (ltrim (substring (line, charindex ('%', line) + 1,
(Charindex ('*', line)-1)-charindex ('%', line) as float), 0) as 'freespace (MB )'
From # output
Where line like '[A-Z] [:] %'
Order by drivename
-- Script to retrieve the values in GB from PS Script output
Select rtrim (ltrim (substring (line, 1, charindex ('|', line)-1) as drivename
, Round (cast (rtrim (ltrim (substring (line, charindex ('|', line) + 1,
(Charindex ('%', line)-1)-charindex ('|', line) as float)/, 0) as 'capacity (GB )'
, Round (cast (rtrim (ltrim (substring (line, charindex ('%', line) + 1,
(Charindex ('*', line)-1)-charindex ('%', line) as float)/1024, 0) as 'freespace (GB )'
From # output
Where line like '[A-Z] [:] %'
Order by drivename
-- Script to drop the temporary table
Drop table # output
