This script builds up on http://www.cookingsql.com/2014/12/free-space-on-drives/ and correlate the data with the location of data files and their sized.
/* DESCRIPTION List space on database files for each database and its location CONFIGURATION none Compatibility list: MSSQL2005 MSSQL2008 MSSQL2012 Issues: Some time it has issues when the database name is too long, such as in some "Sharepoint" servers */ declare @t table ([database_name] sysname, LogicalDBName sysname, type_desc nvarchar(10), 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, type_desc, 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