DataFileSizes.sql

SET NOCOUNT ON
DECLARE @ExtentAllocationsByFileGroup TABLE(Fileid INT NOT NULL
, [FileGroup] INT NOT NULL
, TotalExtents BIGINT NOT NULL
, UsedExtents BIGINT
, [Name] VARCHAR(256)
, [FileName] VARCHAR(256));

INSERT INTO @ExtentAllocationsByFileGroup EXEC('DBCC SHOWFILESTATS WITH NO_INFOMSGS')
INSERT INTO @ExtentAllocationsByFileGroup (Fileid, [FileGroup], TotalExtents, UsedExtents)
SELECT 2, 0, 0, cntr_value/64 FROM sys.dm_os_performance_counters
WHERE counter_name = 'Log File(s) Used Size (KB)'
AND instance_name = DB_NAME()

SELECT t1.name, t2.UsedExtents/16 AS 'Used Space(MB)'
, t1.[size]/128 AS 'Size(MB)'
, t1.[max_size]/128 AS 'Max Size(MB)'
, t1.growth/128 AS 'Growth(MB)'
, CASE 
WHEN t1.[size] + t1.growth > t1.[max_size]
THEN CAST(((1 - t2.UsedExtents/(t1.[size]/8.0))*100) AS INT)
ELSE CAST(((1 - t2.UsedExtents/(t1.[max_size]/8.0))*100) AS INT)
END AS '% Free Space'
, t1.physical_name
FROM sys.master_files t1 INNER JOIN
@ExtentAllocationsByFileGroup t2
ON t1.[file_id] = t2.Fileid
WHERE t1.database_id = DB_ID()
ORDER BY database_id, t1.[type], t1.[name]