I have used the below script many times and used as a starting point for other more specific scenarios like getting information about new servers / databases, auditing and resource allocation. The script maps database files to logical volumes and includes some additional file information for good measure.
/*
-----------------------------------------------------------------
Summary: Server volume to database file mapping
SQL Server Versions: 2005 onwards
Written by: Chris McGowan
-----------------------------------------------------------------
For more SQL resources, check out SQLServer365.blogspot.
-----------------------------------------------------------------
You may alter this code for your own purposes.
You may republish altered code as long as you give due credit.
You must obtain prior permission before blogging this code.
THIS CODE AND INFORMATION ARE PROVIDED "AS IS"
-----------------------------------------------------------------
*/
IF OBJECT_ID('tempdb..#DBFile') IS NULL
CREATE TABLE #DBFile
(
[LogicalName] VARCHAR(200),
[FileID] TINYINT,
[FileName] VARCHAR(1000),
[FileGroup] VARCHAR(100),
[Size] VARCHAR(100),
[MaxSize] VARCHAR(100),
[Growth] VARCHAR(100),
[Usage] VARCHAR(100)
)
GO
IF OBJECT_ID('tempdb..#DBFile2') IS NULL
CREATE TABLE #DBFile2
(
[VolumeLetter] CHAR(3),
[LogicalFileName] VARCHAR(200),
[FileID] TINYINT,
[PhysicalFileName] VARCHAR(1000),
[FileGroup] VARCHAR(100),
[Size] VARCHAR(100),
[MaxSize] VARCHAR(100),
[Growth] VARCHAR(100),
[Usage] VARCHAR(100)
)
GO
EXEC sp_Msforeachdb 'use [?];INSERT INTO #DBFile EXEC sp_helpfile'
GO
INSERT INTO #DBFile2
SELECT LEFT([FileName], 3),
[LogicalName],
[FileID],
[FileName],
[FileGroup],
[Size],
[MaxSize],
[Growth],
[Usage]
FROM #DBFile
GO
SELECT [VolumeLetter],
[LogicalFileName],
[PhysicalFileName],
[FileID],
[FileGroup],
[Size],
[MaxSize],
[Growth],
[Usage]
FROM #DBFile2
ORDER BY [VolumeLetter] ASC
GO
IF OBJECT_ID('tempdb..#DBFile') IS NOT NULL
DROP TABLE #DBFile
IF OBJECT_ID('tempdb..#DBFile2') IS NOT NULL
DROP TABLE #DBFile2
Enjoy
Chris
Hi Chris
ReplyDeleteIf this is only needed to support SQL Server 2005 or Higher then an alternative formulation that allows more flexibility could be:
IF OBJECT_ID('tempdb..#DBFile') IS NULL
CREATE TABLE #DBFile
(
[DatabaseName] VARCHAR(128),
[VolumeLetter] CHAR(3),
[LogicalFileName] VARCHAR(200),
[FileID] TINYINT,
[filegroup_id] SMALLINT,
[PhysicalFileName] VARCHAR(1000),
[FileGroup] VARCHAR(128),
[Size] VARCHAR(100),
[MaxSize] VARCHAR(100),
[Growth] VARCHAR(100),
[Usage] VARCHAR(100)
)
GO
INSERT INTO #DBFile
select
DatabaseName = DB_NAME(database_id),
VolumeLetter = LEFT([physical_name], 3),
LogicalFileName = name,
fileid = file_id,
filegroup_id = data_space_id,
PhysicalFileName = physical_name,
FileGroup = '',
'size' = convert(nvarchar(15), convert (bigint, size) * 8) + N' KB',
'maxsize' = (
case max_size
when -1 then N'Unlimited'
else convert(nvarchar(15), convert (bigint, max_size) * 8) + N' KB'
end),
'growth' = (
case is_percent_growth
when 1 then convert(nvarchar(15), growth) + N'%'
else convert(nvarchar(15), convert (bigint, growth) * 8) + N' KB'
end),
'usage' = (
case type
when 2 then 'log only'
else 'data only'
end)
from sys.master_files
EXEC sp_Msforeachdb 'use [?];UPDATE #DBFile SET FileGroup = FILEGROUP_NAME(filegroup_id) WHERE DB_NAME() = DatabaseName'
Select * from #DBFile
IF OBJECT_ID('tempdb..#DBFile') IS NOT NULL
DROP TABLE #DBFile
This will grab almost all information from sys.master_files. In order to get the FileGroup name then it is necessary to Cursor across each database. This type of script is much easier to extend than having only the output of sp_helpfile.