FILE_NAME (Transact-SQL)

Returns the name of the database file for the specified file ID.

Applies to: SQL Server 2008 and later

Syntax

FILE_NAME ( file_id )

Parameters

Parameter	Description	Required
file_id	The ID of the file for which to return the name. file_id is of type int.	Yes

Return Value

Type: nvarchar(260)
Returns the logical file name. Returns NULL if file_id is invalid or the caller does not have the necessary permissions to view the physical file information.

Permissions

Any user can execute this function. However, the user must have VIEW ANY DEFINITION permission on the database to see the logical file name.

Examples

Example 1: Get the file name for file ID 1

SELECT FILE_NAME(1);

Example 2: Get the file name for all logical files in the current database

SELECT
    name AS LogicalFileName,
    physical_name AS PhysicalFileName,
    FILE_NAME(file_id) AS ReturnedFileName
FROM
    sys.database_files;
                

The following shows the result set for the previous query:

LogicalFileName | PhysicalFileName          | ReturnedFileName
----------------|---------------------------|-----------------
AdventureWorks2012_Data | C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\AdventureWorks2012_Data.mdf | AdventureWorks2012_Data
AdventureWorks2012_Log    | C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\AdventureWorks2012_Log.ldf     | AdventureWorks2012_Log
                

Remarks

You can use FILE_IDEX to find the file ID for a file name.
FILE_NAME returns the logical file name.
To display the file name for a log file, you must specify the file ID of the log file.