USE master
GO
IF OBJECT_ID('sp_files') IS NULL EXEC ('CREATE PROCEDURE sp_files AS SELECT 1 AS ID')
GO
ALTER PROCEDURE dbo.sp_files
@Database SYSNAME = NULL
AS
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
DECLARE @SQL VARCHAR(MAX),
@db_id_str VARCHAR(30)
SELECT @db_id_str = CAST(database_id AS VARCHAR),
@Database = name
FROM sys.databases
WHERE database_id = ISNULL(DB_ID(@Database), DB_ID())
IF OBJECT_ID('TEMPDB..#master_files') IS NOT NULL
DROP TABLE #master_files
IF OBJECT_ID('TEMPDB..#dm_io_virtual_file_stats') IS NOT NULL
DROP TABLE #dm_io_virtual_file_stats
CREATE TABLE #master_files ([database_id] INT NOT NULL,
[file_id] INT NOT NULL,
[FileGroup] SYSNAME NOT NULL,
[name] SYSNAME NOT NULL,
[physical_name] NVARCHAR(260) NOT NULL,
[size] INT NOT NULL,
[is_sparse] BIT NOT NULL,
[data_space_id] INT NOT NULL)
CREATE TABLE #dm_io_virtual_file_stats ([database_id] SMALLINT NOT NULL,
[file_id] SMALLINT NOT NULL,
[sample_ms] INT NOT NULL,
[num_of_reads] BIGINT NOT NULL,
[num_of_bytes_read] BIGINT NOT NULL,
[io_stall_read_ms] BIGINT NOT NULL,
[num_of_writes] BIGINT NOT NULL,
[num_of_bytes_written] BIGINT NOT NULL,
[io_stall_write_ms] BIGINT NOT NULL,
[io_stall] BIGINT NOT NULL,
[size_on_disk_bytes] BIGINT NOT NULL,
[file_handle] VARBINARY(8) NOT NULL)
EXEC('INSERT #master_files
SELECT mf.database_id,
mf.file_id,
ISNULL(fg.name, '''') AS [FileGroup],
mf.name,
mf.physical_name,
mf.size,
mf.is_sparse,
mf.data_space_id
FROM sys.master_files AS mf
LEFT OUTER JOIN ' + @Database + '.sys.filegroups fg ON fg.data_space_id = mf.data_space_id
WHERE mf.database_id = ' + @db_id_str )
SELECT @sql = 'INSERT #dm_io_virtual_file_stats ([database_id], [file_id], [sample_ms], [num_of_reads], [num_of_bytes_read], [io_stall_read_ms], [num_of_writes], [num_of_bytes_written], [io_stall_write_ms], [io_stall], [size_on_disk_bytes], [file_handle])
'
+ Util.dbo.StringConcat('SELECT [database_id], [file_id], [sample_ms], [num_of_reads], [num_of_bytes_read], [io_stall_read_ms], [num_of_writes], [num_of_bytes_written], [io_stall_write_ms], [io_stall], [size_on_disk_bytes], [file_handle] FROM sys.dm_io_virtual_file_stats('
+ CAST(database_id AS VARCHAR) + ', ' + CAST(FILE_ID AS VARCHAR) + ')', '
UNION ALL
')
FROM #master_files
EXEC(@sql)
SELECT mf.FILE_ID,
[FileGroup],
mf.NAME AS [FileName],
mf.physical_name,
CAST(mf.SIZE * 8.0 / 1024 AS NUMERIC(20, 2)) AS sizemb,
Util.dbo.GetPercentage(mf.SIZE, k.SumSize) AS [size%],
CAST(b.size_on_disk_bytes / 1024.0 / 1024 AS NUMERIC(20, 2)) AS size_on_disk_mbytes,
Util.dbo.GetPercentage(b.size_on_disk_bytes, k.Sum_size_on_disk_bytes) AS [size_on_disk%],
Util.dbo.GetPercentage(b.size_on_disk_bytes / 1024.0 / 1024, mf.SIZE * 8.0 / 1024) AS [sparse%%],
mf.is_sparse,
b.num_of_reads,
CAST(b.num_of_bytes_read / 1024.0 / 1024 AS NUMERIC(20, 2)) AS num_of_mbytes_read,
b.io_stall_read_ms,
b.num_of_writes,
CAST(b.num_of_bytes_written / 1024.0 / 1024 AS NUMERIC(20, 2)) AS num_of_mbytes_written,
b.io_stall_write_ms,
b.io_stall
FROM #master_files mf
CROSS JOIN (SELECT (SELECT SUM (SIZE) AS SIZE FROM #master_files) AS SumSize,
ISNULL((SELECT SUM (size_on_disk_bytes) AS Sum_size_on_disk_bytes FROM #dm_io_virtual_file_stats), 0) AS Sum_size_on_disk_bytes) k
LEFT OUTER JOIN #dm_io_virtual_file_stats b ON mf.database_id = b.database_id
AND mf.FILE_ID = b.FILE_ID
GO
EXEC sys.sp_ms_marksystemobject sp_files
GO