USE master GO SET NOCOUNT ON 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 = cast(SERVERPROPERTY ('ComputerNamePhysicalNetBIOS') as varchar(255)) set @sql = 'powershell.exe -c "Get-WmiObject -ComputerName ' + QUOTENAME(@svrName,'''') + ' -Class Win32_Volume -Filter ''DriveType = 3'' | select name,capacity,freespace,Label | foreach{$_.name+''|''+$_.capacity/1048576+''%''+$_.freespace/1048576+''&''+$_.label+''*''}"' --creating a temporary table DECLARE @output TABLE (line varchar(255)) --inserting disk name, total space and free space value in to temporary table insert @output EXEC xp_cmdshell @sql
DECLARE @DISKS TABLE( id int identity ,[DiskName] varchar(10) ,[Capacity(MB)] bigint ,[FreeSpace(MB)] bigint ,[Label] varchar(200) )
INSERT INTO @DISKS 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)' ,rtrim(ltrim(SUBSTRING(line,CHARINDEX('&',line)+1, (CHARINDEX('*',line) -1)-CHARINDEX('&',line)) )) as 'Label' from @output where line like '[A-Z][:]%' order by drivename
select *,[Capacity(MB)]/1024 as [Capacity(GB)],[FreeSpace(MB)]/1024 as [FreeSpace(GB)] ,round( [FreeSpace(MB)]*100/[Capacity(MB)],2) as [% Free] FROM @DISKS
