This script will only list data available on drives. It does not support the use of mount points.

If you need to see how it correlates with the location of data files, see http://www.cookingsql.com/2014/12/data-allocation-per-drive/

/*
DESCRIPTION    List space available in each drive
 
CONFIGURATION    none

 
Compatibility list:
MSSQL2005
MSSQL2008
MSSQL2000

ISSUES:
It does not work for servers with mounted drives as data points
 
*/


-- Check to see if 'Show Advanced Option' is enabled
-- Check to see if 'Ole Automation options' is enabled
 
SET NOCOUNT ON
 
declare @chkOLE as sql_variant
declare @adv_opt as sql_variant
declare @op_ch1 as int
declare @op_ch2 as int
-- these variables keep track if we changed this options, we'll change them back before finishing
 
set @op_ch1=0
set @op_ch2=0
 
select @adv_opt = value from sys.configurations where name = 'show advanced options'
if @adv_opt = 0
begin
 EXEC sp_configure 'show advanced options',1
 RECONFIGURE WITH OVERRIDE;
 set @op_ch1 = 1
end
 
select @chkOLE = value from sys.configurations where name = 'Ole Automation Procedures'
if @chkOLE = 0
begin
 EXEC sp_configure 'Ole Automation Procedures', 1
 RECONFIGURE WITH OVERRIDE;
 set @op_ch2 = 1
end
 
 
--  Starting the script
 
 
DECLARE @hr int
DECLARE @fso int
DECLARE @drive char(1)
DECLARE @odrive int
DECLARE @TotalSize varchar(20)
DECLARE @MB bigint ; SET @MB = 1048576
 
DECLARE @HOSTNAME varchar(20)
 
 select top 1  @HOSTNAME=NodeName from ::fn_virtualservernodes()
IF @HOSTNAME is NULL  set @hostname = cast(serverproperty('ComputerNamePhysicalNetBIOS')as nvarchar(20))
 
 
 
CREATE TABLE #drives (
ServerName varchar(15),
drive char(1) PRIMARY KEY,
FreeSpace int NULL,
TotalSize int NULL,
FreespaceTimestamp DATETIME 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, ServerName = @HOSTNAME, FreespaceTimestamp = (GETDATE())
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
 
SELECT ServerName,
drive,
TotalSize as 'Total(MB)',
FreeSpace as 'Free(MB)',
CAST((FreeSpace/(TotalSize*1.0))*100.0 as int) as 'Free(%)',
FreespaceTimestamp
FROM #drives
ORDER BY drive
 
DROP TABLE #drives
 
---  End of the routine
 
-- Clean up. See if we need to change the settings back.
 
if @op_ch1 = 1
begin
 EXEC sp_configure 'Ole Automation Procedures', 0
 RECONFIGURE WITH OVERRIDE;
end
 
if @op_ch2 = 1
begin
 EXEC sp_configure 'show advanced options',0
 RECONFIGURE WITH OVERRIDE;
 
end