to get the information of used/free space of all SQL Server databases in an instance.

To get the information of used/free space of all SQL Server databases in an instance.


EXEC sp_msforeachdb
'USE [?];
SELECT DB_NAME() AS [Database Name],
    CAST(SUM(CASE WHEN type_desc = ''ROWS'' THEN size END) /128.0 AS DECIMAL(18,2)) AS [Data file size(MB)],
    CAST(SUM(CASE WHEN type_desc = ''LOG'' THEN size END) / 128.0 AS DECIMAL(18,2)) AS [Log file size(MB)],
    CAST(SUM(FILEPROPERTY(name, ''SpaceUsed''))/128.0 AS decimal(18,2)) AS [Used space(MB)],
    CAST(SUM(size)/128.0 AS DECIMAL(18,2)) - CAST(SUM(FILEPROPERTY(name,''SpaceUsed''))/128.0 AS decimal(18,2)) AS [Free space(MB)]  
FROM sys.database_files
GROUP BY type_desc'