Database files information using query

Understanding Database Files in SQL Server

In SQL Server, the `database_files` view provides information about the files associated with a specific database. Each row represents a different file, including details like file type, size, and location. Below is a sample SQL query to retrieve information about database files:

                --- Query to retrieve database file details
                SELECT 
                    f.type_desc AS [File Type], 
                    f.name AS [File Name],
                    fg.name AS [File Group],
                    f.physical_name AS [File Path], 
                    f.size / 128.0 AS [Current Size (MB)], 
                    f.size / 128.0 - CONVERT(INT, FILEPROPERTY(f.name,'SpaceUsed')) / 128.0 AS [Free Space (MB)]
                FROM 
                    sys.database_files f WITH (NOLOCK) 
                LEFT OUTER JOIN 
                    sys.filegroups fg WITH (NOLOCK) ON f.data_space_id = fg.data_space_id
                OPTION (RECOMPILE);
            

SQL Server System Procedures

Another useful system procedure for viewing database file details is `sp_helpfile`. This can provide additional information about the files in a database.

                -- Display information about database files
                sp_helpfile;
            

Useful References

For more detailed information, check out the following Microsoft documentation:

• Sys.Database-Files View
• Sys.Filegroups View
• Sys.Master-Files View

Visual Overview

Here’s a visual representation of the SQL Server database files: