T-SQL to get Get Database Files Usage information

Here is an handy T-SQL script to get Database files usage Information

DECLARE @FreeSpaceThreshold int
SET @FreeSpaceThreshold = 20 -- Change this Threshold Value. Any number between 0 to 100

CREATE TABLE #GetDetails
( 
 ServerName nvarchar(100),
 DatabaseName nvarchar(100),
 LogicalFileName nvarchar(100),
 FileType nvarchar(10),
 FileLocation nvarchar(max),
 [FileSize(MB)] decimal (15,2),
 [UsedSpace(MB)] decimal (15,2),
 [FreeSpace(MB)] decimal (15,2),
 [UsedSpace(%)] decimal (15,2),
 [FreeSpace(%)] decimal (15,2)
 )

EXEC sp_MSforeachdb 'Use [?];
INSERT INTO #GetDetails
SELECT @@SERVERNAME AS ServerName,
    DB_NAME() AS DatabaseName,
    name AS LogicalFileName, 
    type_desc AS FileType, 
    physical_name AS FileLocation,
    CAST(size/128.0 AS decimal(15,2)) AS [FileSize(MB)],
    CAST(FILEPROPERTY(name, ''SpaceUsed'')/128.0 AS decimal(15,2)) AS [UsedSpace(MB)],
    CAST(size/128.0 AS decimal(15,2)) - CAST(FILEPROPERTY(name, ''SpaceUsed'')/128.0 AS decimal(15,2)) AS [FreeSpace(MB)],
    CAST(((FILEPROPERTY(name, ''SpaceUsed'')/128.0 ) /  (size/128.0))*100 AS decimal(15,2)) AS [UsedSpace(%)],
    CAST((((size/128.0) - FILEPROPERTY(name, ''SpaceUsed'')/128.0 ) /  (size/128.0))*100 AS decimal(15,2)) AS [FreeSpace(%)]
FROM sys.database_files'

SELECT * FROM #GetDetails Where [FreeSpace(%)] < @FreeSpaceThreshold
DROP TABLE #GetDetails

Download this script from https://gallery.technet.microsoft.com/T-SQL-to-Get-Database-f16f60c7?redir=0