Get free space in Data and Log files in all databases

To get total size and free space (unallocated space) in transaction log files of all databases we have a very handy command:

DBCC SQLPERF(LOGSPACE)

But there is no such command to get the free space in data files of all databases. Even in current database, data file size and free space can be checked by sp_spaceused.

But if you have many databases than connecting to each database and executing the sp_spaceused command is troublesome.

To simplify this task I have written following query. This provides you all information about file size and free space in all databases.

CREATE TABLE #temp (DatabaseName VARCHAR(100), DataFileSize_MB INT, 

Free_Space_in_Data_Files_MB INT, LogFileSize_MB INT) 

INSERT INTO #temp 

EXEC sp_msforeachdb 'SELECT ''?'' as DatabaseName, DBSize.DataSize, 

case when DBSize.DataSize - allocated.ReservedPages > 0 then 

    DBSize.DataSize - allocated.ReservedPages else 0 end as FreeSpace_MB, DBSize.LogSize 

FROM (SELECT (sum(convert(bigint,case when status & 64 = 0 then 

    size else 0 end))* 8192 / 1048576) DataSize, 

(sum(convert(bigint,case when status & 64 <> 0 then 

    size else 0 end))* 8192 / 1048576) LogSize 

FROM [?].dbo.sysfiles) DBSize, 

(SELECT (sum(total_pages)* 8192 / 1048576) AS ReservedPages 

FROM [?].sys.allocation_units) allocated'

SELECT instance_name AS 'DatabaseName', tt.DataFileSize_MB, tt.Free_Space_in_Data_Files_MB, 

tt.LogFileSize_MB, (tt.LogFileSize_MB -(MAX(cntr_value)/1024)) AS 'Free_Space_in_Log_Files_MB' 

FROM #temp tt inner join sysperfinfo on tt.DatabaseName = sysperfinfo.instance_name 

WHERE counter_name ='Log File(s) Used Size (KB)' AND instance_name != '_total' 

GROUP BY instance_name , tt.DataFileSize_MB, tt.Free_Space_in_Data_Files_MB, tt.LogFileSize_MB 

ORDER BY tt.Free_Space_in_Data_Files_MB + (tt.LogFileSize_MB -(MAX(cntr_value)/1024)) DESC 

--drop table #temp

Following is the screenshot of its output