CREATE VIEW Metadata.DatabaseFiles
AS
SELECT
fg.name COLLATE SQL_Latin1_General_CP1_CI_AS AS filegroup,
mf.type_desc COLLATE SQL_Latin1_General_CP1_CI_AS AS TypeDesc,
mf.name COLLATE SQL_Latin1_General_CP1_CI_AS AS FileName,
CAST(i.size_on_disk_bytes * 1.0 / 1024 / 1024 / 1024 AS NUMERIC(12, 4))AS SizeOnDiskGB,
CAST(mf.SIZE * 8.0 / 1024 / 1024 AS NUMERIC(12, 4))AS SizeGB,
CAST(FILEPROPERTY(mf.name, 'SpaceUsed') / 128.000 / 1024 AS NUMERIC(12, 4))AS UsedGB,
CAST((mf.SIZE - FILEPROPERTY(mf.name, 'SpaceUsed')) / 128.000 / 1024 AS NUMERIC(12, 4))AS FreeGB,
CAST((mf.SIZE - FILEPROPERTY(mf.name, 'SpaceUsed')) * 100.00 / mf.SIZE AS NUMERIC(12, 4))AS [Free%],
CAST(NULLIF(mf.max_size, -1) * 8.0 / 1024 / 1024 AS NUMERIC(12, 4))AS MaxSizeGB,
CAST(CASE
WHEN mf.is_percent_growth = 0
THEN mf.growth
END / 128.000 AS NUMERIC(12, 4))AS GrowthMB,
CASE
WHEN mf.is_percent_growth = 1
THEN mf.growth
END AS [Growth%],
i.sample_ms AS SampleMs,
i.num_of_reads AS NumOfReads,
CAST(i.num_of_bytes_read * 1.0 / 1024 / 1024 AS NUMERIC(12, 4))AS ReadMB,
i.io_stall_read_ms AS IOStallReadMs,
i.num_of_writes AS NumOfWrites,
CAST(i.num_of_bytes_written * 1.0 / 1024 AS NUMERIC(20, 2))AS WrittenMB,
i.io_stall_write_ms AS IOStallWriteMS,
i.io_stall AS IOStall,
mf.state_desc COLLATE SQL_Latin1_General_CP1_CI_AS AS StateDesc,
mf.is_media_read_only AS IsMediaReadOnly,
mf.is_read_only AS IsReadOnly,
mf.is_sparse AS IsSparse,
mf.is_percent_growth AS IsPercentGrowth,
mf.data_space_id AS DataSpaceId,
mf.FILE_ID AS FileId,
mf.type AS Type,
mf.physical_name COLLATE SQL_Latin1_General_CP1_CI_AS AS PhysicalName,
SUBSTRING(mf.physical_name, 1, LEN(mf.physical_name) - CHARINDEX('\', REVERSE(mf.physical_name)))COLLATE SQL_Latin1_General_CP1_CI_AS AS [Path]
FROM sys.database_files AS mf(NOLOCK)
LEFT OUTER JOIN sys.data_spaces AS ds(NOLOCK)ON mf.data_space_id = ds.data_space_id
LEFT OUTER JOIN sys.filegroups AS fg(NOLOCK)ON ds.data_space_id = fg.data_space_id
OUTER APPLY Metadata.GetIOVirtualFileStats(DB_ID(), mf.FILE_ID)AS i