List information about all database files in SQL Server

Question

Can I list file information (MDF / LDF) of all databases on SQL Server?

I would like to get a list showing which database uses files on the local drive.

What I tried:

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

Answer 1

You can use sys.master_files .

Contains a string in the database file stored in the main database. This is a single system-wide view.

Answer 2

If you want to get the database location, you can check Get all database location .
you can use sys.master_files to get db location and sys.databse 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 

Answer 3

I use a 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.

Answer 4

Executing the following sql (it will only work if you do not have multiple mdf / ldf files for the 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 conclusion

 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 other databases

If your TempDB database has multiple MDFs (like mine), 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.

Answer 5

You can also try this.

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

Answer 6

I 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 

Answer 7

This script lists most of what you are looking for, and I hope you can change it. Note that a permanent table is created there - you can change it. This is a subset of a larger script that also summarizes backup and job information on different 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] 

Answer 8

The query will generate an error if several data files are used in one of the databases (for example, file types ".ndf").

Here's a version of your query that uses joins instead of subqueries.

Hurrah!

 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 

Answer 9

The script below can be used to obtain the following information: 1. Information about the size of the database 2. FileSpaceInfo 3. AutoGrowth 4. Recovery model 5. Information Log_reuse_backup

 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 

Answer 10

With this script you can show all used databases and files (with the exception of system dbs). Perform this using master db

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

Answer 11

You can use below:

 SP_HELPDB [Master] GO