You are here

Database File Space and Free Space

/*
 
    Author: Ben Farnsworth
    Date:   1/7/14
     
    Get info on Data files
 
*/
IF OBJECT_ID('tempdb..#space') IS NOT NULL DROP TABLE #space
create table #space(
    name varchar(100)
    ,filename varchar(200)
    ,filesizeMB decimal(12,2)
    ,spaceusedMB decimal(12,2)
    ,freespaceMB decimal(12,2)
)
 
exec sp_msforeachdb @command1 = '
use [?]
INSERT INTO #space
select
      name
    , filename
    , convert(decimal(12,2),round(a.size/128.000,2)) as FileSizeMB
    , convert(decimal(12,2),round(fileproperty(a.name,''SpaceUsed'')/128.000,2)) as SpaceUsedMB
    , convert(decimal(12,2),round((a.size-fileproperty(a.name,''SpaceUsed''))/128.000,2)) as FreeSpaceMB
from dbo.sysfiles a
 
'
 
select * from #space
 
order by freespaceMB desc
 
 
IF OBJECT_ID('tempdb..#space') IS NOT NULL DROP TABLE #space
Categories: 
CAPTCHA
This question is for testing whether or not you are a human visitor and to prevent automated spam submissions.
4 + 1 =
Solve this simple math problem and enter the result. E.g. for 1+3, enter 4.

Theme by Danetsoft and Danang Probo Sayekti inspired by Maksimer