SET NOCOUNT ON
DECLARE @hr int
DECLARE @fso int
DECLARE @drive char(1)
DECLARE @odrive int
DECLARE @TotalSize varchar(20) DECLARE @MB Numeric ; SET @MB = 1048576
CREATE TABLE #drives (drive char(1) PRIMARY KEY, FreeSpace int NULL, TotalSize int NULL) 

INSERT #drives(drive,FreeSpace) 
EXEC master.dbo.xp_fixeddrives 

EXEC @hr=sp_OACreate 'Scripting.FileSystemObject',@fso OUT 

IF @hr <> 0 
EXEC sp_OAGetErrorInfo @fso

DECLARE dcur CURSOR LOCAL FAST_FORWARD
FOR SELECT drive from #drives ORDER by drive
OPEN dcur FETCH NEXT FROM dcur INTO @drive
WHILE @@FETCH_STATUS=0
BEGIN
	EXEC @hr = sp_OAMethod @fso,'GetDrive', @odrive OUT, @drive
	IF @hr <> 0 
	EXEC sp_OAGetErrorInfo @fso EXEC @hr =	sp_OAGetProperty @odrive,'TotalSize', @TotalSize OUT 
	IF @hr <> 0 EXEC sp_OAGetErrorInfo	@odrive 
	UPDATE #drives 
	SET TotalSize=@TotalSize/@MB 
	WHERE	drive=@drive FETCH NEXT FROM dcur INTO @drive
End
Close dcur
DEALLOCATE dcur
	EXEC @hr=sp_OADestroy @fso 
	IF @hr <> 0 
	EXEC sp_OAGetErrorInfo @fso

		DECLARE @tableHTML  	NVARCHAR(MAX)
		DECLARE @subject1  	NVARCHAR(110)

		SET @tableHTML = '<p>Hello All,</p> <p>The drive free space details.</p>'+
		'<table border="1" cellspacing="0" cellpadding="0" >' +
		'<tr align="center"><td><strong>Server Name</strong></td><td><strong>Date</strong></td>' +
		'<td><strong>Drive</strong></td>'+
		'<td><strong>Total(GB)</strong></td><td><strong>Free(GB)</strong></td>' +
		'<td><strong>Free Space (in %)</strong></td>'+
		'</tr><TR align="center">' +
		CAST ( ( SELECT td = @@servername, '',
						td = convert(varchar,getdate(),106), '',
						td = Drive, '',
						td = convert(decimal(7,2),TotalSize/1024.0), '',
						td = convert(decimal(7,2),FreeSpace/1024.0), '',
						td = convert(decimal(7,2),(convert(decimal(7,2),FreeSpace/1024.0)/(TotalSize/1024.0))*100), ''
			from	#drives 
			--where  	convert(decimal(7,2),(convert(decimal(7,2),FreeSpace/1024.0)/(TotalSize/1024.0))*100) < 25.00
				  FOR XML PATH('TR'), TYPE 
		) AS NVARCHAR(MAX) ) +
		N'</tr></table>' 

	select @subject1 = @@servername + ' Drive space Details'

	if @tableHTML is not NULL and @subject1 is not null
	begin 
	EXEC msdb.dbo.sp_send_dbmail
	@body_format='HTML',
	@body=@tableHTML, 
	@recipients=N'EMail id ',
	@subject = @subject1,@profile_name ='Profile'

	End


DROP TABLE #drives 

SET NOCOUNT OFF