In SQL Server, you can use the FILE_IDEX() function to return the ID of a given database file.

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

Example 1 – Basic Usage

Here’s a quick example to demonstrate.

USE WideWorldImportersDW;
SELECT FILE_IDEX('WWI_Primary') AS Result;

Result:

+----------+
| Result   |
|----------|
| 1        |
+----------+

Example 2 – More Files

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

USE WideWorldImportersDW;
SELECT 
  FILE_IDEX('WWI_Primary') AS WWI_Primary,
  FILE_IDEX('WWI_Log') AS WWI_Log,
  FILE_IDEX('WWI_UserData') AS WWI_UserData;

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_IDEX('Music') AS Music,
  FILE_IDEX('Music_Log') AS Music_Log,
  FILE_IDEX('Music_UserData') AS Music_UserData;

Result:

+---------+-------------+------------------+
| Music   | Music_Log   | Music_UserData   |
|---------+-------------+------------------|
| 1       | 2           | NULL             |
+---------+-------------+------------------+

The file names are different for this database. Also, regarding the third column, there’s no file with that name, so we get a NULL result.

Example 4 – Using sys.database_files

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

USE WideWorldImportersDW;
SELECT file_id
FROM sys.database_files
WHERE name = 'WWI_Primary';

Result:

+-----------+
| file_id   |
|-----------|
| 1         |
+-----------+

Example 5 – Using sys.master_files

Here’s a similar query for sys.master_files:

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

Result:

+-----------+
| file_id   |
|-----------|
| 1         |
+-----------+

This view is a system-wide view and it can return data from all databases. Therefore, I add the current database (by using DB_ID()) into the query’s WHERE clause.