列出有关 SQLServer 中所有数据库文件的信息

You can use sys.master_files.

Contains a row per file of a database as stored in the master database. This is a single, system-wide view.

I am using script to get empty space in each file:

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

Size is expressed in KB.

If you want get location of Database you can check Get All DBs Location.
you can use sys.master_files for get location of db and sys.database to get db name

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

Executing following sql (It will only work when you don't have multiple mdf/ldf files for same database)

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

will return this output

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

and rest of the databases

If your TempDB's have multiple MDF's (like mine have), this script will fail. However, you can use

WHERE db.database_id > 4

at the end and it will return all databases except system databases.

You can use the below:

SP_HELPDB [Master]
GO

You can also try this.

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

This script lists most of what you are looking for and can hopefully be modified to you needs. Note that it is creating a permanent table in there - you might want to change it. It is a subset from a larger script that also summarises backup and job information on various servers.

IF OBJECT_ID('tempdb..#DriveInfo') IS NOT NULL
DROP TABLE #DriveInfo
CREATE TABLE #DriveInfo
(
Drive CHAR(1)
,MBFree INT
)


INSERT  INTO #DriveInfo
EXEC master..xp_fixeddrives




IF OBJECT_ID('[dbo].[Tmp_tblDatabaseInfo]', 'U') IS NOT NULL
DROP TABLE [dbo].[Tmp_tblDatabaseInfo]
CREATE TABLE [dbo].[Tmp_tblDatabaseInfo](
[ServerName] [nvarchar](128) NULL
,[DBName] [nvarchar](128)  NULL
,[database_id] [int] NULL
,[create_date] datetime NULL
,[CompatibilityLevel] [int] NULL
,[collation_name] [nvarchar](128) NULL
,[state_desc] [nvarchar](60) NULL
,[recovery_model_desc] [nvarchar](60) NULL
,[DataFileLocations] [nvarchar](4000)
,[DataFilesMB] money null
,DataVolumeFreeSpaceMB INT NULL
,[LogFileLocations] [nvarchar](4000)
,[LogFilesMB] money null
,LogVolumeFreeSpaceMB INT NULL


) ON [PRIMARY]


INSERT INTO [dbo].[Tmp_tblDatabaseInfo]
SELECT
@@SERVERNAME AS [ServerName]
,d.name AS DBName
,d.database_id
,d.create_date
,d.compatibility_level
,CAST(d.collation_name AS [nvarchar](128)) AS collation_name
,d.[state_desc]
,d.recovery_model_desc
,(select physical_name + ' | ' AS [text()]
from sys.master_files m
WHERE m.type = 0 and m.database_id = d.database_id
ORDER BY file_id
FOR XML PATH ('')) AS DataFileLocations
,(select sum(size) from sys.master_files m WHERE m.type = 0 and m.database_id = d.database_id)  AS DataFilesMB
,NULL
,(select physical_name + ' | ' AS [text()]
from sys.master_files m
WHERE m.type = 1 and m.database_id = d.database_id
ORDER BY file_id
FOR XML PATH ('')) AS LogFileLocations
,(select sum(size) from sys.master_files m WHERE m.type = 1 and m.database_id = d.database_id)  AS LogFilesMB
,NULL
FROM  sys.databases d


WHERE d.database_id > 4 --Exclude basic system databases
UPDATE [dbo].[Tmp_tblDatabaseInfo]
SET DataFileLocations =
CASE WHEN LEN(DataFileLocations) > 4 THEN  LEFT(DataFileLocations,LEN(DataFileLocations)-2) ELSE NULL END
,LogFileLocations =
CASE WHEN LEN(LogFileLocations) > 4 THEN  LEFT(LogFileLocations,LEN(LogFileLocations)-2) ELSE NULL END
,DataFilesMB =
CASE WHEN DataFilesMB > 0 THEN  DataFilesMB * 8 / 1024.0   ELSE NULL END
,LogFilesMB =
CASE WHEN LogFilesMB > 0 THEN  LogFilesMB * 8 / 1024.0  ELSE NULL END
,DataVolumeFreeSpaceMB =
(SELECT MBFree FROM #DriveInfo WHERE Drive = LEFT( DataFileLocations,1))
,LogVolumeFreeSpaceMB =
(SELECT MBFree FROM #DriveInfo WHERE Drive = LEFT( LogFileLocations,1))


select * from [dbo].[Tmp_tblDatabaseInfo]

To get around queries which error when multiple data files (e.g. ".ndf" file types) exist, try this version, it replaces the sub-queries with joins.

Here's a version of your query using joins instead of the sub-queries.

Cheers!

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

Sample Results: (Please note, the single log file is paired with each MDF and NDF for a single database)

I've created this query:

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

Below script can be used to get following information: 1. DB Size Info 2. FileSpaceInfo 3. AutoGrowth 4. Recovery Model 5. Log_reuse_backup information

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

Using this script you can show all the databases name and files used (with exception of system dbs).

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

just adding my 2 cents .

if specifically looking to find total free space only in Data files or only in Log files in all the databases, we can use "data_space_id" column. 1 is for data files and 0 for log files.

CODE:

Create Table ##temp
(
DatabaseName sysname,
Name sysname,
spacetype sysname,
physical_name nvarchar(500),
size decimal (18,2),
FreeSpace decimal (18,2)
)
Exec sp_msforeachdb '
Use [?];


Insert Into ##temp (DatabaseName, Name,spacetype, physical_name, Size, FreeSpace)
Select DB_NAME() AS [DatabaseName], Name,   ***data_space_id*** , physical_name,
Cast(Cast(Round(cast(size as decimal) * 8.0/1024.0,2) as decimal(18,2))/1024 as nvarchar) SizeGB,
Cast(Cast(Round(cast(size as decimal) * 8.0/1024.0,2)/1024 as decimal(18,2)) -
Cast(FILEPROPERTY(name, ''SpaceUsed'') * 8.0/1024.0 as decimal(18,2))/1024 as nvarchar) As FreeSpaceGB
From sys.database_files'




select
databasename
, sum(##temp.FreeSpace)
from
##temp
where
##temp.spacetype = 1
group by
DatabaseName


drop table ##temp

If you rename your Database, MS SQL Server does not rename the underlying files.

Following query gives you the current name of the database and the Logical file name (which might be the original name of the Database when it was created) and also corresponding physical file names.

Note: Un-comment the last line to see only the actual data files

select  db.database_id,
db.name "Database Name",
files.name "Logical File Name",
files.physical_name
from    sys.master_files files
join sys.databases db on db.database_id = files.database_id
--                           and files.type_desc = 'ROWS'

Reference:

https://learn.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-master-files-transact-sql?view=sql-server-ver15

https://learn.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-databases-transact-sql?view=sql-server-ver15

Using the sp_MSForEachDB stored procedure is an option

EXEC sp_MSForEachDB 'use ? select * from sys.database_files'

Additionally to see just the Full Path name and size information

EXEC sp_MSForEachDB '
USE [?];
SELECT DB_NAME() AS DbName,
physical_name AS FullPath,
name AS FileName,
type_desc,
size/128.0 AS CurrentSizeMB,
size/128.0 - CAST(FILEPROPERTY(name, ''SpaceUsed'') AS INT)/128.0 AS FreeSpaceMB
FROM sys.database_files
WHERE type IN (0,1);
'

Also you can use this SQL query for retrieving files list :

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 ;