SQL Server Database File Size and Free Space Revisited
I recently had a comment posted to one of my first articles I wrote way back in 2012 concerning adding a column that detailed the used space as a %age. So without any further ado, here is an updated version of a script originally blogged about here.
select
sf.FILEID AS [File ID],
[File Size in MB] = convert(decimal(12,2),round(sf.size/128.000,2)),
[Max Size in MB] = convert(decimal(12,2),round(sf.maxsize/128.000,2)),
[Space Used in MB] = convert(decimal(12,2),round(fileproperty(sf.name,'SpaceUsed')/128.000,2)),
[Free Space in MB] = convert(decimal(12,2),round((sf.size-fileproperty(sf.name,'SpaceUsed'))/128.000,2)) ,
[Free Space in %] = convert(decimal(12,2),round(100*(sf.size-fileproperty(sf.name,'SpaceUsed'))/128.000,2)/(sf.size/128.000)) ,
[Used Space in %] = convert(decimal(12,2),round(100*(fileproperty(sf.name,'SpaceUsed'))/128.000,2)/(sf.size/128.000)) ,
[File Name] = left(sf.NAME,30),
[File Location] = left(sf.FILENAME,100)
from dbo.sysfiles sf
order by fileid asc