Today is the 1st February 2012 and as promised here is the first of script in my "A Script A Day" series. There will be 29 scripts (yes this year is a leap year:) which I will also roll-up into a single document and make available to you some time in March.
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.com
-----------------------------------------------------------------
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
