Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- declare @svrName varchar(255)
- declare @sql varchar(400)
- 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
- 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)/1024,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
- declare @svrName varchar(255)
- declare @sql varchar(400)
- 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
- 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)/1024,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][:]%'
- having (CHARINDEX('*',line) -1)-CHARINDEX('%',line)) )) as Float) /1024 ,0) < 20
- order by drivename
- declare @svrName varchar(255)
- declare @sql varchar(400)
- 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
- 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)/1024,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)'
- into #output_2
- from #output
- where line like '[A-Z][:]%'
- order by drivename
- select *
- from #output_2
- where [freespace(GB)] < 20
Add Comment
Please, Sign In to add comment