Lists the database files that comprise a single database, including their size, and space used

 

set nocount on
create table #Data(
      FileID int NOT NULL,
      [FileGroupId] int NOT NULL,
      TotalExtents int NOT NULL,
      UsedExtents int NOT NULL,
      [FileName] sysname NOT NULL,
      [FilePath] nvarchar(MAX) NOT NULL,
      [FileGroup] varchar(MAX) NULL)

create table #Results(
      db sysname NULL ,
      FileType varchar(4) NOT NULL,
      [FileGroup] sysname not null,
      [FileName] sysname NOT NULL,
      TotalMB numeric(18,2) NOT NULL,
      UsedMB numeric(18,2) NOT NULL,
      PctUsed numeric(18,2) NULL,
      FilePath nvarchar(MAX) NULL,
      FileID int null)

create table #Log(
      db sysname NOT NULL,
      LogSize numeric(18,5) NOT NULL,
      LogUsed numeric(18,5) NOT NULL,
      Status int NOT NULL,
      [FilePath] nvarchar(MAX) NULL)

INSERT #Data (FileID, [FileGroupId], TotalExtents, UsedExtents, [FileName], [FilePath])
EXEC ('DBCC showfilestats WITH NO_INFOMSGS')

update #Data
set #Data.FileGroup = sysfilegroups.groupname
from #Data, sysfilegroups
where #Data.FileGroupId = sysfilegroups.groupid

INSERT INTO #Results (db, [FileGroup], FileType, [FileName], TotalMB, UsedMB, PctUsed, FilePath, FileID)
SELECT DB_NAME() db,
            [FileGroup],
            'Data' FileType,
            [FileName],
            TotalExtents * 64./1024. TotalMB,
            UsedExtents *64./1024 UsedMB,
            UsedExtents*100. /TotalExtents  UsedPct,
            [FilePath],
            FileID
FROM #Data
order BY --1,2
DB_NAME(), [FileGroup]

insert #Log (db,LogSize,LogUsed,Status)
exec('dbcc sqlperf(logspace) WITH NO_INFOMSGS ')

insert #Results(db, [FileGroup], FileType, [FileName],  TotalMB,UsedMB, PctUsed, FilePath, FileID)
select DB_NAME() db,
            'Log' [FileGroup],
            'Log' FileType,
            s.[name] [FileName],
            s.Size/128. as LogSize ,
            FILEPROPERTY(s.name,'spaceused')/8.00 /16.00 As LogUsedSpace,
            ((FILEPROPERTY(s.name,'spaceused')/8.00 /16.00)*100)/(s.Size/128.) UsedPct,
            s.FileName FilePath,
            s.FileID FileID
      from #Log l , master.dbo.sysaltfiles f , dbo.sysfiles s
      where f.dbid = DB_ID()
      and (s.status & 0x40) <> 0
      and s.FileID = f.FileID
      and l.db = DB_NAME()
 
SELECT r.db AS "Database",
r.FileType AS "File type",
CASE
 WHEN r.FileGroup = 'Log' Then 'N/A'
 ELSE r.FileGroup
END "File group",
r.FileName AS "Logical file name",
r.TotalMB AS "Total size (MB)",
r.UsedMB AS "Used (MB)",
r.PctUsed AS "Used (%)",
r.FilePath AS "File name",
r.FileID AS "File ID",
CASE WHEN s.maxsize = -1 THEN null
    ELSE CONVERT(decimal(18,2), s.maxsize /128.)
END "Max. size (MB)",
CONVERT(decimal(18,2), s.growth /128.) "Autogrowth increment (MB)"
FROM #Results r
INNER JOIN dbo.sysfiles s
ON r.FileID = s.FileID
ORDER BY 1,2,3,4,5
 
DROP TABLE #Data
DROP TABLE #Results
DROP TABLE #Log