SQL Server Get free space in all files in an Instance

Pensa numa consulta completa para quem precisa saber o quanto um Database está reservando de espaço em seu arquivo, assim como o tamanho dos arquivos mdf e ldf.

Com algumas melhorias aplicadas na rotina da fonte: https://www.sqlservergeeks.com/sql-server-get-free-space-in-all-files-in-an-instance/

DECLARE @DBName NVARCHAR(100) = NULL, 
        @Drive NVARCHAR(2) = NULL 
 
DECLARE @cmd NVARCHAR(4000)
IF (SELECT OBJECT_ID('tempdb.dbo.#DBName')) IS NOT NULL
DROP TABLE #DBName
CREATE TABLE #DBName (Name NVARCHAR(100))
 
IF @DBName IS NOT NULL
INSERT INTO #DBName SELECT @DBName
ELSE
INSERT INTO #DBName SELECT Name FROM sys.databases WHERE state_desc = 'ONLINE'
 
IF (SELECT OBJECT_ID('tempdb.dbo.##FileStats')) IS NOT NULL
DROP TABLE ##FileStats
CREATE TABLE ##FileStats (ServerName NVARCHAR(100), DBName NVARCHAR(100), FileType NVARCHAR(100), 
FileName NVARCHAR(100), CurrentSizeMB FLOAT, FreeSpaceMB FLOAT, PercentMBFree FLOAT, FileLocation NVARCHAR(1000))
 
WHILE (SELECT TOP 1 * FROM #DBName) IS NOT NULL
BEGIN
 
    SELECT @DBName = MIN(Name) FROM #DBName
 
    SET @cmd = 'USE [' + @DBName + ']
    INSERT INTO ##FileStats
    SELECT @@ServerName AS ServerName, DB_NAME() AS DbName, 
    CASE WHEN type = 0 THEN ''DATA'' ELSE ''LOG'' END AS FileType,
    name AS FileName, 
     size/128.0 AS CurrentSizeMB,
 --ROUND(CAST(((size*8) / 1024.0 / 1024.0) AS FLOAT),2) AS CurrentSizeMB,  
    (size/128.0 - CAST(FILEPROPERTY(name, ''SpaceUsed'') AS INT)/128.0) AS FreeSpaceMB,
    100*(1 - ((CAST(FILEPROPERTY(name, ''SpaceUsed'') AS INT)/128.0)/(size/128.0))) AS PercentMBFree,
    physical_name AS FileLocation
    FROM sys.database_files
 '
     
    IF @Drive IS NOT NULL
    SET @cmd = @cmd + ' WHERE physical_name LIKE ''' + @Drive + ':\%'''
 
    EXEC sp_executesql @cmd
     
    DELETE FROM #DBName WHERE Name = @DBName
     
END
 
SELECT 
 ServerName,
 DBName,
 ROUND(CAST((CurrentSizeMB  / 1024.0) AS FLOAT),2) AS "FileSize_GB",
 ROUND(CAST((FreeSpaceMB*100/CurrentSizeMB) AS FLOAT),2) AS "PercentFree", 
 ROUND(CAST((FreeSpaceMB  / 1024.0) AS FLOAT),2) AS "FreeSpace_GB",
 FileName,
 FileLocation
FROM ##FileStats
--WHERE FileLocation LIKE 'D:%'
ORDER BY CurrentSizeMB DESC
DROP TABLE #DBName
DROP TABLE ##FileStats