logical and physical file names of all databases

  • 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

  • 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

  • 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
  • Thanks, the easy query works better.

  • How about SELECT *

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

    Easy!

  • 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 ?

  • 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.

  • nevermind

  • 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".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

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

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

  • 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".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

    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