How to List the Database Name and File Locations

List SQL Server Database Names and File Locations

Knowing exactly where your SQL Server database files live is fundamental to good database administration. You can retrieve the database name, logical file names, and physical file paths for every database on an instance by querying sys.sysfiles within each database context, or by using the system stored procedure sp_msforeachdb to loop across all databases at once.

This information matters more than most people realise until something goes wrong.

Why Does This Matter?

Consider the scenarios where you urgently need to know your database file locations: a storage volume is filling up, you're planning a migration to new hardware, you need to verify a backup strategy covers the right paths, or you're auditing an environment you've inherited and have no documentation for. In all of these situations, you need accurate, current file location data fast.

Many SQL Server environments accumulate databases over years of application deployments, migrations, and restores. Files end up scattered across multiple drives, sometimes on volumes with different performance characteristics or backup schedules. Without a reliable way to list database names and file locations across the entire instance, you're flying blind.

This is one of the first queries any experienced DBA runs when they sit down in front of an unfamiliar SQL Server instance.

How Do You List All Database File Locations in SQL Server?

The quickest way to get database name and file location information across all databases is with sp_msforeachdb. This undocumented but widely used system stored procedure iterates over every database on the instance and executes the SQL you provide, substituting ? with the current database name.

Here's the classic version of that query:

EXECUTE sp_msforeachdb 'USE ?; SELECT DB_NAME(); SELECT name, filename FROM [?].sys.sysfiles'

This returns two result sets per database: the database name, then the logical file names and physical file paths for that database. It works on SQL Server 2005 through to current versions, though sys.sysfiles is a compatibility view. We'll cover the preferred modern approach shortly.

A More Useful Version of the Query

The basic sp_msforeachdb version works, but the output is fragmented across dozens of result sets, which makes it hard to read or export. A consolidated query using sys.databases and sys.master_files from the master database is cleaner and more practical for most purposes.

SELECT
    d.name                          AS DatabaseName,
    mf.name                         AS LogicalName,
    mf.type_desc                    AS FileType,
    mf.physical_name                AS PhysicalFilePath,
    CAST(mf.size * 8.0 / 1024 AS DECIMAL(10,2)) AS FileSizeMB,
    mf.state_desc                   AS FileState
FROM
    sys.databases d
    INNER JOIN sys.master_files mf ON d.database_id = mf.database_id
ORDER BY
    d.name,
    mf.type_desc DESC;

This query runs entirely from the master database context and returns a single, consolidated result set covering every database on the instance. It's far easier to copy into a spreadsheet, filter, or hand to a storage team.

The columns returned give you everything you need at a glance:

• DatabaseName - the SQL Server database name
• LogicalName - the logical name SQL Server uses internally to reference the file
• FileType - either ROWS (data file) or LOG (transaction log file)
• PhysicalFilePath - the full path to the file on disk
• FileSizeMB - current allocated file size in megabytes
• FileState - whether the file is ONLINE, OFFLINE, RESTORING, and so on

When Should You Use sp_msforeachdb vs sys.master_files?

Both approaches have their place.

Use sp_msforeachdb when you need to run database-specific queries that require switching context into each database, for example when querying sys.sysfiles directly or running other per-database checks as part of a broader health check script.

Use sys.master_files when you just need file location and size information. It's simpler, faster, returns a single result set, and doesn't carry the occasional quirks of sp_msforeachdb, which can skip databases under certain conditions (particularly if a database name contains unusual characters or the database is in a non-standard state).

For production environments, the sys.master_files approach is the more reliable choice for auditing and documentation purposes.

What About the sys.sysfiles Compatibility View?

The original script uses sys.sysfiles, which is a compatibility view retained from SQL Server 2000. It still works in modern SQL Server versions, but Microsoft's documentation notes it may be removed in a future release. The preferred replacement is sys.database_files, which is queried within the context of each database and returns equivalent information.

If you're maintaining legacy scripts that reference sys.sysfiles, it's worth updating them to use sys.database_files or sys.master_files instead. This is a straightforward change and removes a dependency on deprecated objects.

Practical Uses for This Query

Once you can reliably list database names and file locations across your instance, a number of common DBA tasks become much easier.

Storage audits. Identify which volumes are hosting database files and cross-reference against available free space. A single query gives you the data you need to have an informed conversation with your storage team.

Migration planning. Before moving databases to new hardware or a new SQL Server instance, you need a complete inventory of all data and log file paths. Missing a file during a migration causes failures that are embarrassing and avoidable.

Backup verification. Confirm that your backup jobs are targeting the correct paths. If files have been moved or restored to non-standard locations, your backup scripts may be referencing paths that no longer match reality.

Environment documentation. Inherited an undocumented SQL Server environment? Running this query is one of the first steps toward building an accurate picture of what you're managing. Combined with queries against sys.databases for recovery model, compatibility level, and owner, you can build a solid baseline document in under an hour.

Identifying non-standard file placement. In well-managed environments, data files and log files live on separate volumes, often with specific naming conventions. This query quickly reveals databases where files have ended up on the wrong drive, perhaps after a restore that defaulted to a different path.

Permissions Required

To query sys.master_files, you need the VIEW ANY DATABASE permission or membership in the sysadmin fixed server role. Standard logins without elevated permissions will only see databases they have access to, which can lead to an incomplete picture if you're running this as a non-privileged account.

Always run file location queries under a login with sufficient permissions to see all databases on the instance. In most managed environments, this means running as a member of sysadmin or a dedicated monitoring account with VIEW SERVER STATE and VIEW ANY DATABASE granted explicitly.

Key Takeaways

• Use sys.master_files joined to sys.databases for a clean, consolidated view of all database file locations across an entire SQL Server instance.
• The sp_msforeachdb approach works but produces fragmented output and can occasionally skip databases in non-standard states.
• sys.sysfiles is a deprecated compatibility view. Replace references to it with sys.database_files or sys.master_files in any scripts you maintain.
• Knowing your database file locations is essential for storage audits, migration planning, backup verification, and environment documentation.
• Always run file location queries under an account with VIEW ANY DATABASE permission to ensure you see all databases on the instance.

Queries like this one form the foundation of any SQL Server health check. At DBA Services, our health check engagements include a full audit of database file placement, sizing, and growth settings as standard, because file location problems that go unnoticed have a habit of becoming outages. If you'd like a thorough review of your SQL Server environment, or ongoing managed support to keep on top of these details, get in touch with our team.