Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

logical and physical file names of all databases Expand / Collapse
Author
Message
Posted Sunday, April 3, 2011 8:00 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 9:59 AM
Points: 1,755, Visits: 3,162
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
Post #1087890
Posted Sunday, April 3, 2011 10:04 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, July 25, 2014 1:26 PM
Points: 347, Visits: 360
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.[size] 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.[size] 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




Post #1087900
Posted Sunday, April 3, 2011 11:28 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 8:47 AM
Points: 42,479, Visits: 35,547
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 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1087905
Posted Monday, April 4, 2011 9:39 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 9:59 AM
Points: 1,755, Visits: 3,162
Thanks, the easy query works better.
Post #1088145
Posted Friday, April 19, 2013 5:11 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, March 3, 2014 2:58 AM
Points: 3, Visits: 65
How about SELECT *
FROM [master].[dbo].[sysaltfiles] for SQL Server 2000?

Easy!
Post #1444313
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse