Listing information about all database files in SQL Server

Is it possible to list information about the files (MDF/LDF) of all databases on an SQL Server?

I'd like to get a list showing which database is using what files on the local disk.

What I tried:

• exec sp_databases all databases
• select * from sys.databases shows a lot of information about each database - but unfortunately it doesn't show the files used by each database.
• select * from sys.database_files shows the mdf/ldf files of the master database - but not the other databases

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.

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

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.

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

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 also try this.

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

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

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

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)

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://docs.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-master-files-transact-sql?view=sql-server-ver15

https://docs.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);
'

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 

You can use the below:

SP_HELPDB [Master]
GO