USE master
GO
IF OBJECT_ID('sp_DBFileInfo') IS NULL EXEC ('CREATE PROCEDURE sp_DBFileInfo AS SELECT 1 AS ID')
GO
ALTER PROCEDURE dbo.sp_DBFileInfo
@DatabaseIncludeList VARCHAR(MAX) = NULL
AS
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
DECLARE @SQL VARCHAR(MAX)
IF OBJECT_ID('tempdb..##DatabaseFiles') IS NOT NULL
DROP TABLE ##DatabaseFiles
CREATE TABLE ##DatabaseFiles ([DBName] SYSNAME NOT NULL,
[filegroup] SYSNAME NULL,
FileName SYSNAME NOT NULL,
[physicalname] VARCHAR(260) NOT NULL,
[PATH] VARCHAR(260) NULL,
[sizegb] NUMERIC(12, 4) NULL,
[usedgb] NUMERIC(12, 4) NULL,
[freegb] NUMERIC(12, 4) NULL,
[freegb%] NUMERIC(12, 4) NULL,
Instance VARCHAR(128) NULL)
PRINT 'SELECT * FROM ##DatabaseFiles
' ;
WITH Incl
AS (SELECT LTRIM(RTRIM(Field)) AS DBName
FROM Util.dbo.ParseDelimited(@DatabaseIncludeList, ','))
SELECT @SQL = Util.dbo.StringConcat('
USE ' + name + '
INSERT ##DatabaseFiles ([Instance], [DBName], [filegroup], FileName, [physicalname], [path], [sizegb], [usedgb], [freegb], [freegb%])
SELECT
' + ISNULL('''' + CAST(SERVERPROPERTY('InstanceName') AS VARCHAR(128)) + '''', 'NULL') + ' AS InstanceName,
''' + name + ''' AS [database],
fg.name AS filegroup,
mf.name AS FileName,
mf.physical_name as physicalname,
SUBSTRING(mf.physical_name, 1, LEN(mf.physical_name) - CHARINDEX(''\'', REVERSE(mf.physical_name))) AS path,
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 [freegb%]
FROM [' + name + '].sys.database_files mf (NOLOCK)
LEFT OUTER JOIN [' + name + '].sys.data_spaces ds (NOLOCK) ON mf.data_space_id = ds.data_space_id
LEFT OUTER JOIN [' + name + '].sys.filegroups fg (NOLOCK) ON ds.data_space_id = fg.data_space_id
', '') + '
SELECT * FROM ##DatabaseFiles'
FROM master.sys.databases d (NOLOCK)
WHERE (EXISTS ( SELECT *
FROM Incl
WHERE DBname = d.NAME )
OR NOT EXISTS ( SELECT *
FROM incl ))
AND STATE = 0
EXEC(@SQL)
GO
EXEC sys.sp_ms_marksystemobject sp_DBFileInfo
GO