SQL Server find Datafile which is less than 10% Space

 

CREATE TABLE #FileSize

(dbName NVARCHAR(128), 

    FileName NVARCHAR(128), 

    type_desc NVARCHAR(128),

    CurrentSizeMB DECIMAL(10,2), 

    FreeSpaceMB DECIMAL(10,2),

FreeSpacePercentage DECIMAL(10,2)

);

    

INSERT INTO #FileSize(dbName, FileName, type_desc, CurrentSizeMB, FreeSpaceMB,FreeSpacePercentage)

exec sp_msforeachdb 

'use [?]; 

SELECT DB_NAME() AS DbName,

    name AS FileName,

type_desc,

    CAST((size * 8.0 / 1024) AS DECIMAL(10, 2)) AS CurrentSizeMB,

    CAST(((size - FILEPROPERTY(name, ''SpaceUsed'')) * 8.0 / 1024) AS DECIMAL(10, 2)) AS FreeSpaceMB,

    CAST((((size - FILEPROPERTY(name, ''SpaceUsed'')) * 100.0) / size) AS DECIMAL(10, 2)) AS FreeSpacePercentage

FROM sys.database_files

WHERE type IN (0,1);';

    

SELECT * 

FROM #FileSize

WHERE dbName NOT IN ('distribution', 'master', 'model', 'msdb')

AND FreeSpacePercentage < 10;

    

DROP TABLE #FileSize;