This script joins two scripts

•  http://www.cookingsql.com/2014/12/free-space-on-drives/
•  http://www.cookingsql.com/2014/12/data-file-sizes-and-free-space/

We first get the free space o drives, and correlate the data (inner join) with the location of data files

 /*
DESCRIPTION    List space on database files for each database and its location
 
CONFIGURATION    none
 
Compatibility list:
MSSQL2008R2 and higher

Issues: Some time it has issues when the database name is too long, such as in some "Sharepoint" servers
 
*/

 /*
DESCRIPTION    List space on database files for each database and its location
 
CONFIGURATION    none
 
Compatibility list:
MSSQL2008R2 and higher



Issues: Some time it has issues when the database name is too long, such as in some "Sharepoint" servers
 
*/

/* This section checks if xp_cmdshell is enabled, and keeps track of the settings */ 
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 will 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
 
/*
This section queries space avail on each drive.
*/
-- ==============
 
DECLARE @cmd2 NVARCHAR(2000)
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)
 
-- checks if this is a cluster
select top 1  @HOSTNAME=nodename from ::fn_virtualservernodes()
IF @HOSTNAME is NULL set @hostname = cast(ServerProperty('machinename') as nvarchar(10))
 
declare @temp table 
(servername nvarchar(100), database_name nvarchar(100),driveletter nvarchar(2),totaldriveMB int,
 freedriveMB int,FreeDiskPct nvarchar(3), filename nvarchar(100), filesizeMB nvarchar(10), freeSpaceMB int,
FreeSpacePct varchar(8),AutoGrowth nvarchar(100), MaxsizeMB int, phys nvarchar(100))
 
declare @drives TABLE
(ServerName varchar(15),  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, ServerName = @HOSTNAME
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
 
 
 /* This section turns off the xp_cmdshell settings if needed  */
 
if @op_ch2 = 1
begin
 EXEC sp_configure 'Ole Automation Procedures', 0
 RECONFIGURE WITH OVERRIDE;
end
 
if @op_ch1 = 1
begin
 EXEC sp_configure 'show advanced options',0
 RECONFIGURE WITH OVERRIDE;
 
end



/* This section queries every database for space stats.*/  

declare @t table 
([database_name] sysname, LogicalDBName sysname, sizeMB bigint, FreeSpaceMB bigint,
freespace nvarchar(4), Autogrow nvarchar(100), MAxSize bigint, physname nvarchar(max))

declare @cmd nvarchar (2000)
set @cmd =
'use [?]
SELECT 
''?'',
b.name,
CAST(b.size/128.0 AS int) ''SizeMB'', 
CAST(b.size/128.0 - CAST(FILEPROPERTY(b.name, ''SpaceUsed'' ) AS int)/128.0 AS int) ''FreeSpaceMB'', 
CAST(ceiling(100 * (b.size/128.0 -CAST(FILEPROPERTY(b.name,''SpaceUsed'' ) AS int)/128.0)/(b.size/128.0)) AS varchar(8)) + ''%''  ''FreeSpace'',
    CASE 
        WHEN b.is_percent_growth = 0 
            THEN LTRIM(STR(b.growth * 8.0 / 1024,10,1)) + '' MB, '' 
        ELSE
            ''By '' + CAST(b.growth AS VARCHAR) + '' percent, ''
    END + 
    CASE 
        WHEN b.max_size = -1 THEN ''unrestricted growth''
        ELSE ''restricted growth to '' + 
            LTRIM(STR(b.max_size * 8.0 / 1024,10,1)) + '' MB'' 
    END AS Autogrow,
cast(b.max_size/128.0 as int) ''MaxSize'',
physical_name

FROM [?].sys.database_files b'
insert into @t
Exec sp_MSForEachDB @cmd

-----------------------------------


select * 
from @t t
inner join @drives d on left(t.physname,1)=d.drive