In SQL Server, you can use the FILE_NAME() function to return the logical file name of a given database file.

To do this, pass the file ID to the function. This is the ID that corresponds to the file_id column in the sys.master_files catalog view or the sys.database_files catalog view. Those views also contain the logical file name, but the FILE_NAME() function saves you from having to query those views.

Example 1 – Basic Usage

Here’s a quick example to demonstrate.

USE WideWorldImportersDW;
SELECT FILE_NAME(1) AS Result;

Result:

+-------------+
| Result      |
|-------------|
| WWI_Primary |
+-------------+

Example 2 – More Files

Here’s another example, this time returning three files.

USE WideWorldImportersDW;
SELECT 
  FILE_NAME(1) AS [File 1], 
  FILE_NAME(2) AS [File 2], 
  FILE_NAME(3) AS [File 3];

Result:

+-------------+----------+--------------+
| File 1      | File 2   | File 3       |
|-------------+----------+--------------|
| WWI_Primary | WWI_Log  | WWI_UserData |
+-------------+----------+--------------+

Example 3 – Different Database

In this example I switch to a different database, then run the query again.

USE Music;
SELECT 
  FILE_NAME(1) AS [File 1], 
  FILE_NAME(2) AS [File 2], 
  FILE_NAME(3) AS [File 3];

Result:

+----------+-----------+----------+
| File 1   | File 2    | File 3   |
|----------+-----------+----------|
| Music    | Music_log | NULL     |
+----------+-----------+----------+

In this case, there’s no file with an ID of 3, so I get a NULL result for that column.

Example 4 – Using sys.database_files

As mentioned, the FILE_NAME() function saves you from having to query the sys.database_files or sys.master_files views. If we didn’t have the FILE_NAME() function, we’d probably have to do something like this:

SELECT name
FROM sys.database_files
WHERE file_id = 2;

Result:

+-----------+
| name      |
|-----------|
| Music_log |
+-----------+

Example 5 – Using sys.master_files

If we used sys.master_files, we’d need to add some code to specify which database:

SELECT name 
FROM sys.master_files
WHERE file_id = 2
AND database_id = DB_ID();

Result:

+-----------+
| name      |
|-----------|
| Music_log |
+-----------+

With this view, if you don’t specify the database ID, you’ll get results from all databases. Therefore I specify which database in the WHERE clause. In this case I use the DB_ID() function to get the name of the current database.