April 3, 2011 at 8:00 pm
Is there a query or sp that I can pull all databases on the server along with their logical file names and physical file names?
Thanks
April 3, 2011 at 10:04 pm
Try this code...
USE [master]
GO
CREATE PROCEDURE [dbo].[dba_rpt_SQL_Files_Report]
AS
/*
By Norm Enger - 2007 - http://thesqlguy.wordpress.com/
Reports on the file info for all dbs on a SQL instance, SQL 2000 and above
EXEC dba_rpt_SQL_Files_Report
*/
SET NOCOUNT ON
DECLARE @cmd1 VARCHAR(8000)
DECLARE @cmd2 VARCHAR(8000)
DECLARE @cmd3 VARCHAR(8000)
CREATE TABLE #data(
dbname VARCHAR(300),
dbcompat VARCHAR(10),
Fileid int NOT NULL,
[FileGroup] int NOT NULL,
TotalExtents int NOT NULL,
UsedExtents int NOT NULL,
[Name] sysname NOT NULL,
[FileName] varchar(300) NOT NULL,
FilePath VARCHAR(500) NULL,
Autogrowth VARCHAR(500) NULL,
init_size_MB DECIMAL (38,7) NULL,
tempdb_growth_MB DECIMAL (38,7) NULL
)
CREATE TABLE #log(
id int identity(1,1) not null,
groupid INT DEFAULT 0 NULL,
dbname sysname NOT NULL,
dbcompat VARCHAR(10),
--LogSize numeric(15,7) NOT NULL,
LogSize numeric(38,7) NOT NULL,
--LogUsed numeric(9,5) NOT NULL,
LogUsed numeric(38,6) NOT NULL,
Status int NOT NULL,
[Name] VARCHAR(300) NULL,
FilePath VARCHAR(500) NULL,
Autogrowth VARCHAR(500) NULL,
init_size_MB DECIMAL (38,7) NULL,
tempdb_growth_MB DECIMAL (38,7) NULL
)
DECLARE @DBs table (name sysname, status varchar(100), dbcompat varchar(10))
DECLARE @CurrDB varchar(500)
DECLARE @CurrCmptlevel varchar(10)
INSERT INTO @DBs
(name, dbcompat)
SELECT name, cmptlevel
FROM sysdatabases
WHERE category IN ('0', '1','16')
AND CAST(DATABASEPROPERTYEX (name, 'status') AS VARCHAR(500)) = 'ONLINE'
ORDER BY name
SELECT @CurrDB = MIN(name) FROM @DBs
SELECT @CurrCmptlevel = dbcompat FROM @DBs WHERE name = @CurrDB
--Data files
WHILE @CurrDB IS NOT NULL
BEGIN
SET @cmd1 = 'USE [' + @CurrDB + ']; INSERT #data (Fileid, FileGroup, TotalExtents, UsedExtents, Name, FileName) EXEC(''DBCC showfilestats with no_infomsgs''); UPDATE #data SET dbname = ''' + @CurrDB + ''', dbcompat = ''' + CAST(@CurrCmptlevel AS VARCHAR(10)) + ''' WHERE dbname IS NULL; update #data set [FilePath] = s.filename, Autogrowth = ''Autogrowth: '' + CASE WHEN (s.status & 0x100000 = 0 AND CEILING((s.growth * 8192.0)/(1024.0*1024.0)) = 0.00) OR s.growth = 0 THEN ''None'' WHEN s.status & 0x100000 = 0 THEN ''By '' + CONVERT(VARCHAR,CEILING((s.growth * 8192.0)/(1024.0*1024.0))) + '' MB'' ELSE ''By '' + CONVERT(VARCHAR, s.growth) + '' percent'' END + CASE WHEN (s.status & 0x100000 = 0 AND CEILING((s.growth * 8192.0)/(1024.0*1024.0)) = 0.00) OR s.growth = 0 THEN '''' WHEN CAST([maxsize]*8.0/1024 AS DEC(20,2)) <= 0.00 THEN '', unrestricted growth'' ELSE '', restricted growth to '' + CAST(CAST([maxsize]*8.0/1024 AS DEC(20)) AS VARCHAR) + '' MB'' END from #data d inner join dbo.sysfiles s on d.FileGroup = s.groupid and d.Fileid = s.fileid where d.dbname = ''' + @CurrDB + ''''
EXEC (@cmd1)
SELECT @CurrDB = MIN(name) FROM @DBs WHERE name > @CurrDB
SELECT @CurrCmptlevel = dbcompat FROM @DBs WHERE name = @CurrDB
END
SELECT @CurrDB = MIN(name) FROM @DBs
SELECT @CurrCmptlevel = dbcompat FROM @DBs WHERE name = @CurrDB
--Log files
WHILE @CurrDB IS NOT NULL
BEGIN
--Update log file logical names
SET @cmd3 = 'USE [' + @CurrDB + ']; INSERT #log (dbname, LogSize, LogUsed, Status) EXEC(''DBCC sqlperf(logspace) with no_infomsgs''); update #log set [Name] = s.name, [FilePath] = s.filename, Autogrowth = ''Autogrowth: '' + CASE WHEN (s.status & 0x100000 = 0 AND CEILING((s.growth * 8192.0)/(1024.0*1024.0)) = 0.00) OR s.growth = 0 THEN ''None'' WHEN s.status & 0x100000 = 0 THEN ''By '' + CONVERT(VARCHAR,CEILING((s.growth * 8192.0)/(1024.0*1024.0))) + '' MB'' ELSE ''By '' + CONVERT(VARCHAR, s.growth) + '' percent'' END + CASE WHEN (s.status & 0x100000 = 0 AND CEILING((s.growth * 8192.0)/(1024.0*1024.0)) = 0.00) OR s.growth = 0 THEN '''' WHEN CAST([maxsize]*8.0/1024 AS DEC(20,2)) <= 0.00 THEN '', unrestricted growth'' ELSE '', restricted growth to '' + CAST(CAST([maxsize]*8.0/1024 AS DEC(20)) AS VARCHAR) + '' MB'' END from #log l inner join dbo.sysfiles s on l.groupid = s.groupid where l.dbname = ''' + @CurrDB + '''; update #log set dbcompat = ''' + @CurrCmptlevel + ''' where dbname = ''' + @CurrDB + ''''
EXEC (@cmd3)
SELECT @CurrDB = MIN(name) FROM @DBs WHERE name > @CurrDB
SELECT @CurrCmptlevel = dbcompat FROM @DBs WHERE name = @CurrDB
END
--Clean up #log
DELETE #log where Name IS NULL
DELETE #log FROM #log l
lEFT JOIN
(
SELECT dbname, Name, MIN(id) AS id FROM #log GROUP BY dbname, Name
) k
ON l.dbname = k.dbname AND l.Name = k.Name AND l.id = k.id
WHERE k.id IS NULL
--Get tempdb initial file sizes - update tempdb data files init sizes
UPDATE [#data]
SET [init_size_MB] = a.Initial_Size_MB
FROM #data d
INNER JOIN
(
SELECT
[DB_Name] = 'tempdb',
f.[name] AS logical_file_Name,
(CAST(f. AS DECIMAL) * 8192) / 1024 /1024 AS Initial_Size_MB
FROM master..sysaltfiles f
INNER JOIN master..sysdatabases d
ON f.dbid = d.dbid
WHERE d.name = 'tempdb') AS a
ON d.dbname = a.[DB_Name]
AND d.[Name] = a.logical_file_Name
--Get tempdb initial file sizes - update tempdb log file init sizes
UPDATE [#log]
SET [init_size_MB] = a.Initial_Size_MB
FROM #log l
INNER JOIN
(
SELECT
[DB_Name] = 'tempdb',
f.[name] AS logical_file_Name,
(CAST(f. AS DECIMAL) * 8192) / 1024 /1024 AS Initial_Size_MB
FROM master..sysaltfiles f
INNER JOIN master..sysdatabases d
ON f.dbid = d.dbid
WHERE d.name = 'tempdb') AS a
ON l.dbname = a.[DB_Name]
AND l.[Name] = a.logical_file_Name
--Calculate tempdb files growth
UPDATE [#data]
SET [tempdb_growth_MB] =
CASE
WHEN ((TotalExtents*64)/1024.0) - init_size_MB < 0.00 THEN 0.00
ELSE ((TotalExtents*64)/1024.0) - init_size_MB
END
UPDATE [#log]
SET [tempdb_growth_MB] =
CASE
WHEN LogSize - init_size_MB < 0.00 THEN 0.00
ELSE LogSize - init_size_MB
END
--Return results for all dbs
SELECT
dbname + ' (' + CAST(DATABASEPROPERTYEX (dbname, 'recovery') AS VARCHAR(500)) + ') (' + dbcompat + ')' AS [DB_NAME],
dbname AS DB_NAME_2,
'Data File' AS [Type],
[Name] AS [NAME],
[FilePath],
init_size_MB,
(TotalExtents*64)/1024.0 AS [TotalMB],
tempdb_growth_MB,
(UsedExtents*64)/1024.0 AS [UsedMB],
(TotalExtents*64)/1024.0 - (UsedExtents*64)/1024.0 AS [FreeMB],
[Autogrowth]
FROM #data
UNION
SELECT
dbname + ' (' + CAST(DATABASEPROPERTYEX (dbname, 'recovery') AS VARCHAR(500)) + ') (' + dbcompat + ')' AS [DB_NAME],
dbname AS DB_NAME_2,
'TLog File',
[Name] AS [NAME],
[FilePath],
init_size_MB,
LogSize,
tempdb_growth_MB,
((LogUsed/100)*LogSize),
LogSize - ((LogUsed/100)*LogSize),
[Autogrowth]
FROM #log
ORDER BY 1, [Type], [Name]
DROP TABLE #data
DROP TABLE #log
GO
April 3, 2011 at 11:28 pm
Sure, easy query.
SELECT DB_NAME(database_id) AS DatabaseName, name AS LogicalFileName, physical_name AS PhysicalFileName
FROM sys.master_files AS mf
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 4, 2011 at 9:39 am
Thanks, the easy query works better.
April 19, 2013 at 5:11 am
How about SELECT *
FROM [master].[dbo].[sysaltfiles] for SQL Server 2000?
Easy!
February 13, 2015 at 12:53 am
SQLCMD -S . -d Operdb -E -Q “SELECT DB_NAME(database_id) AS DatabaseName, name AS LogicalFileName, physical_name AS PhysicalFileName
FROM sys.master_files AS mf” -s “,” -o “C:\result.csv”
it come outside from command prompt.. how to use ?
March 15, 2016 at 9:55 am
I deleted my database via Management Studio and was attempting to re-create it with the same name and I get the "logical filename <...> is already in use". I looked at sys.master_files and didn't see any remnants, but there must be somewhere. How do I clean this up, so I can use the same name? Thanks.
March 15, 2016 at 10:02 am
nevermind
March 15, 2016 at 11:17 pm
lesemv (3/15/2016)
nevermind
What did you find to make you say "nevermind"?
--Jeff Moden
Change is inevitable... Change for the better is not.
March 16, 2016 at 6:00 am
I'm embarrassed to say. I had the same 'Name' value for the data and log file.
March 16, 2016 at 7:26 am
lesemv (3/16/2016)
I'm embarrassed to say. I had the same 'Name' value for the data and log file.
Heh... thanks. I'm sure that none of us have ever done that before. Good to see that I'm not the only one. :blush:
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 11 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy