Post reply

logical and physical file names of all databases

  • April 3, 2011 at 8:00 pm

    #389190

    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

    #1306848

    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

    #1306852

    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

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

  • April 4, 2011 at 9:39 am

    #1307056

    Thanks, the easy query works better.

  • April 19, 2013 at 5:11 am

    #1608146

    How about SELECT *

    FROM [master].[dbo].[sysaltfiles] for SQL Server 2000?

    Easy!

  • February 13, 2015 at 12:53 am

    #1777420

    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

    #1865312

    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

    #1865322

    nevermind

  • March 15, 2016 at 11:17 pm

    #1865509

    lesemv (3/15/2016)

    nevermind

    What did you find to make you say "nevermind"?

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • March 16, 2016 at 6:00 am

    #1865564

    I'm embarrassed to say. I had the same 'Name' value for the data and log file.

  • March 16, 2016 at 7:26 am

    #1865591

    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

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 11 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Cookies on SQLServerCentral

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