/* Database space usage for instance */
IF OBJECT_ID('tempdb..#DatabaseFileSpace') IS NOT NULL DROP TABLE #DatabaseFileSpace ;
CREATE TABLE #DatabaseFileSpace
(
ID INT IDENTITY(1, 1),
DatabaseName varchar(128),
LogicalFileName varchar(128),
FileGroupName nvarchar(128),
PhysicalFileName varchar(128),
FileSizeMB float,
SpaceUsedMB float,
FreeSpaceMB float
) ;
EXEC dbo.sp_MSforeachdb '
use ?
INSERT INTO #DatabaseFileSpace
select
''?'' as DatabaseName
,a.name as LogicalFileName
,isnull(g.name, ''LOG'') as FileGroupName
,filename as PhysicalFileName
,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 [?].sys.sysfiles a
left join [?].sys.filegroups g on a.groupid = g.data_space_id
'
select *
from #DatabaseFileSpace
order by DatabaseName, FileGroupName
select
DatabaseName,
FileGroupName,
SUM(FileSizeMB) as FileSizeMB,
SUM(SpaceUsedMB) as SpaceUsedMB,
SUM(FreeSpaceMB) as FreeSpaceMB
from #DatabaseFileSpace
group by DatabaseName, FileGroupName
order by DatabaseName, FileGroupName
select
DatabaseName,
SUM(FileSizeMB) as FileSizeMB,
SUM(SpaceUsedMB) as SpaceUsedMB,
SUM(FreeSpaceMB) as FreeSpaceMB
from #DatabaseFileSpace
group by DatabaseName
order by DatabaseName
