列出SQL Server中所有数据库文件的信息

Question

如何在SQL Server上列出所有数据库（MDF/LDF）文件的信息？

我想获取一个列表，显示哪个数据库正在使用本地磁盘上的哪些文件。

我的尝试：

• exec sp_databases 显示所有数据库
• select * from sys.databases 显示每个数据库的大量信息 - 但不幸的是它不显示每个数据库使用的文件。
• select * from sys.database_files 显示master数据库的mdf/ldf文件- 但不包括其他数据库。

- M4N

---

我发现如果你在select * from sys.database_files之前加上一个指向你感兴趣的数据库的use语句，它就能正常工作。所以，在SQL Server 2019中，use MyDb; select * from sys.database_files对我来说是有效的。 - undefined

Answer 1

你可以使用 sys.master_files 。

它包含数据库中每个文件的行，这些行在主数据库中存储。这是一个单一的、系统范围内的视图。

- Mikael Eriksson

---

谢谢，这（与sys.databases结合使用）正是我一直在寻找的！ - M4N

选择 * 从 sys.master_files - Cosmin

如果您只想获取数据库名称，您也可以调用DB_NAME(database_id)而不是连接sys.databases。 - Cleptus

Answer 2

如果您想获取数据库的位置，可以查看获取所有数据库位置。
您可以使用sys.master_files获取数据库的位置，并使用sys.database获取数据库名称。

SELECT
    db.name AS DBName,
    type_desc AS FileType,
    Physical_Name AS Location
FROM
    sys.master_files mf
INNER JOIN 
    sys.databases db ON db.database_id = mf.database_id

- Mehdi Haghshenas

Answer 3

我正在使用脚本获取每个文件中的空白空间：

Create Table ##temp
(
    DatabaseName sysname,
    Name sysname,
    physical_name nvarchar(500),
    size decimal (18,2),
    FreeSpace decimal (18,2)
)   
Exec sp_msforeachdb '
Use [?];
Insert Into ##temp (DatabaseName, Name, physical_name, Size, FreeSpace)
    Select DB_NAME() AS [DatabaseName], Name,  physical_name,
    Cast(Cast(Round(cast(size as decimal) * 8.0/1024.0,2) as decimal(18,2)) as nvarchar) Size,
    Cast(Cast(Round(cast(size as decimal) * 8.0/1024.0,2) as decimal(18,2)) -
        Cast(FILEPROPERTY(name, ''SpaceUsed'') * 8.0/1024.0 as decimal(18,2)) as nvarchar) As FreeSpace
    From sys.database_files
'
Select * From ##temp
drop table ##temp

大小以KB为单位表示。

- Jānis

---

“Use [?]” 应该是做什么用的？它会报错，说找不到那个存储过程。如果将其删除，则只会显示多个系统数据库。 - Abel

大小不是以MB为单位吗？ - Henrik Staun Poulsen

Answer 4

我创建了这个查询：

SELECT 
    db.name AS                                   [Database Name], 
    mf.name AS                                   [Logical Name], 
    mf.type_desc AS                              [File Type], 
    mf.physical_name AS                          [Path], 
    CAST(
        (mf.Size * 8
        ) / 1024.0 AS DECIMAL(18, 1)) AS         [Initial Size (MB)], 
    'By '+IIF(
            mf.is_percent_growth = 1, CAST(mf.growth AS VARCHAR(10))+'%', CONVERT(VARCHAR(30), CAST(
        (mf.growth * 8
        ) / 1024.0 AS DECIMAL(18, 1)))+' MB') AS [Autogrowth], 
    IIF(mf.max_size = 0, 'No growth is allowed', IIF(mf.max_size = -1, 'Unlimited', CAST(
        (
                CAST(mf.max_size AS BIGINT) * 8
        ) / 1024 AS VARCHAR(30))+' MB')) AS      [MaximumSize]
FROM 
     sys.master_files AS mf
     INNER JOIN sys.databases AS db ON
            db.database_id = mf.database_id

- Alexandru-Codrin Panaite

---

谢谢，只是针对那些不支持IIF的旧版本，只需替换最后2列的计算即可。 - Mer

根据mf.is_percent_growth的值，如果为1，则返回mf.growth的值加上百分号；否则将(mf.growth * 8) / 1024.0的结果转换为DECIMAL(18, 1)类型，并转换为VARCHAR(30)类型，最后加上MB单位。同时，根据mf.max_size的值，如果为0，则返回“No growth is allowed”；如果为-1，则返回“Unlimited”；否则将mf.max_size乘以8再除以1024，将结果转换为BIGINT类型，最后转换为VARCHAR(30)类型并加上MB单位。 - Mer

Answer 5

执行以下 SQL（仅适用于同一数据库没有多个 MDF/LDF 文件的情况）

SELECT
    db.name AS DBName,
    (select mf.Physical_Name FROM sys.master_files mf where mf.type_desc = 'ROWS' and db.database_id = mf.database_id ) as DataFile,
    (select mf.Physical_Name FROM sys.master_files mf where mf.type_desc = 'LOG' and db.database_id = mf.database_id ) as LogFile
FROM sys.databases db

将返回此输出

DBName       DataFile                     LogFile
--------------------------------------------------------------------------------
master       C:\....\master.mdf           C:\....\mastlog.ldf
tempdb       C:\....\tempdb.mdf           C:\....\templog.ldf
model        C:\....\model.mdf            C:\....\modellog.ldf

和其他数据库

如果您的TempDB有多个MDF文件（像我的一样），这个脚本将失败。 但是，您可以使用

WHERE db.database_id > 4

在查询语句的末尾加上这行代码，它会返回除了系统数据库之外的所有数据库。

- adeel41

---

我知道这是一个小数据集，但这并不是使用相关子查询的理由。它们在Oracle上可能很好用，但在SQL Server上它们会严重影响性能，因为它们会导致逐行处理。你的脚本将为sys.databases表中的每一行查询两次sys.master_files表。 - Davos

除了Davos的评论之外，如果您有任何数据库的多个数据文件或日志文件，此脚本也会出现错误。（例如：子查询返回多个值。） - Arkaine55

@Davos 我知道你的意思，但这取决于你执行此查询的频率，否则这就是预优化，可能你不需要。 - adeel41

我一般同意早期优化不好这个观点，但我想说的是相关子查询只是一种本来就不应该使用的糟糕模式。总会有例外，但这不是其中之一。虽然这在这里可能微不足道并且可能并不重要，但这不是关键点。这是一个新手用来学习良好实践的公共论坛，因此您需要提供示范代码。 - Davos

如果在其中一个数据库中使用多个数据文件，则查询将出错。这里是使用连接的查询版本。干杯！SELECT db.name AS DBName, db.database_id, mfr.Physical_Name AS DataFile, mfl.Physical_Name AS LogFile FROM sys.databases db JOIN sys.master_files mfr ON db.database_id=mfr.database_id AND mfr.type_desc='ROWS' JOIN sys.master_files mfl ON db.database_id=mfl.database_id AND mfl.type_desc='LOG' ORDER BY db.database_id - Robert

Answer 6

使用此脚本，您可以显示所有数据库名称和使用的文件（除系统数据库外）。

select name,physical_name from sys.master_files where database_id > 4

- Jorge Cribb

Answer 7

以下脚本可用于获取以下信息： 1. 数据库大小信息 2. 文件空间信息 3. 自动增长信息 4. 恢复模式 5. 日志重用备份信息

CREATE TABLE #tempFileInformation
(
DBNAME          NVARCHAR(256),
[FILENAME]      NVARCHAR(256),
[TYPE]          NVARCHAR(120),
FILEGROUPNAME   NVARCHAR(120),
FILE_LOCATION   NVARCHAR(500),
FILESIZE_MB     DECIMAL(10,2),
USEDSPACE_MB    DECIMAL(10,2),
FREESPACE_MB    DECIMAL(10,2),
AUTOGROW_STATUS NVARCHAR(100)
)
GO

DECLARE @SQL VARCHAR(2000)

SELECT @SQL = '
 USE [?]
            INSERT INTO #tempFileInformation
            SELECT  
                DBNAME          =DB_NAME(),     
                [FILENAME]      =A.NAME,
                [TYPE]          = A.TYPE_DESC,
                FILEGROUPNAME   = fg.name,
                FILE_LOCATION   =a.PHYSICAL_NAME,
                FILESIZE_MB     = CONVERT(DECIMAL(10,2),A.SIZE/128.0),
                USEDSPACE_MB    = CONVERT(DECIMAL(10,2),(A.SIZE/128.0 - ((A.SIZE - CAST(FILEPROPERTY(A.NAME,''SPACEUSED'') AS INT))/128.0))),
                FREESPACE_MB    = CONVERT(DECIMAL(10,2),(A.SIZE/128.0 -  CAST(FILEPROPERTY(A.NAME,''SPACEUSED'') AS INT)/128.0)),
                AUTOGROW_STATUS = ''BY '' +CASE is_percent_growth when 0 then cast (growth/128 as varchar(10))+ '' MB - ''
                                                                  when 1 then cast (growth as varchar(10)) + ''% - '' ELSE '''' END
                                                                  + CASE MAX_SIZE WHEN 0 THEN '' DISABLED '' 
                                                                                  WHEN -1 THEN '' UNRESTRICTED''
                                                                                  ELSE '' RESTRICTED TO '' + CAST(MAX_SIZE/(128*1024) AS VARCHAR(10)) + '' GB '' END
                                                                + CASE IS_PERCENT_GROWTH WHEn 1 then '' [autogrowth by percent]'' else '''' end
    from sys.database_files A
    left join sys.filegroups fg on a.data_space_id = fg.data_space_id
    order by A.type desc,A.name
    ;
    '

    --print @sql

    EXEC sp_MSforeachdb @SQL
    go

    SELECT dbSize.*,fg.*,d.log_reuse_wait_desc,d.recovery_model_desc
    FROM #tempFileInformation fg
    LEFT JOIN sys.databases d on fg.DBNAME = d.name
    CROSS APPLY
    (
        select dbname,
                sum(FILESIZE_MB) as [totalDBSize_MB],
                sum(FREESPACE_MB) as [DB_Free_Space_Size_MB],
                sum(USEDSPACE_MB) as [DB_Used_Space_Size_MB]
            from #tempFileInformation
            where  dbname = fg.dbname
            group by dbname
    )dbSize


go
DROP TABLE #tempFileInformation

- Vinod Narwal

Answer 8

你也可以尝试这个。

 select db_name(dbid) dbname, filename from sys.sysaltfiles

- Joe Pi

Answer 9

为了解决查询时出现错误的问题，当存在多个数据文件（例如“.ndf”文件类型）时，请尝试使用以下版本，它将子查询替换为连接。

这是使用连接而不是子查询的查询版本。

干杯！

SELECT
    db.name AS DBName,
    db.database_id,
    mfr.physical_name AS DataFile,
    mfl.physical_name AS LogFile
FROM sys.databases db
    JOIN sys.master_files mfr ON db.database_id=mfr.database_id AND mfr.type_desc='ROWS'
    JOIN sys.master_files mfl ON db.database_id=mfl.database_id AND mfl.type_desc='LOG'
ORDER BY db.database_id

示例结果： （请注意，每个单一的日志文件都与每个MDF和NDF配对，用于单一数据库）

- Robert

Answer 10

你也可以使用以下 SQL 查询来检索文件列表：

SELECT d.name AS DatabaseName, 
       m.name AS LogicalName, 
       m.physical_name AS PhysicalName, 
       size AS FileSize
FROM sys.master_files m
     INNER JOIN sys.databases d ON(m.database_id = d.database_id)
     where d.name = '<Database Name>'
ORDER BY physical_name ;

- Shahriar Khazaei