FILE_IDEX (Transact-SQL)
Applies to:
SQL Server
Azure SQL Managed Instance
This function returns the file identification (ID) number for the specified logical name of a data, log, or full-text file of the current database.
Transact-SQL syntax conventions
Syntax
FILE_IDEX ( file_name )
Note
To view Transact-SQL syntax for SQL Server 2014 (12.x) and earlier versions, see Previous versions documentation.
Arguments
file_name
An expression of type sysname that returns the file ID value 'FILE_IDEX' for the name of the file.
Return Types
int
NULL on error
Remarks
file_name corresponds to the logical file name displayed in the name column from the sys.master_files or sys.database_files catalog views.
Use FILE_IDEX
in a SELECT list, a WHERE clause, or anywhere that supports use of an expression. For more information, see Expressions (Transact-SQL).
Examples
A. Retrieving the file id of a specified file
This example returns the file ID for the AdventureWorks_Data
file.
USE AdventureWorks2022;
GO
SELECT FILE_IDEX('AdventureWorks2022_Data') AS 'File ID';
GO
Here is the result set.
File ID
-------
1
(1 row(s) affected)
B. Retrieving the file id when the file name is not known
This example returns the file ID of the AdventureWorks
log file. The Transact-SQL (T-SQL) code snippet selects the logical file name from the sys.database_files
catalog view, where the file type equals 1
(log).
USE AdventureWorks2022;
GO
SELECT FILE_IDEX((SELECT TOP (1) name FROM sys.database_files WHERE type = 1)) AS 'File ID';
GO
Here is the result set.
File ID
-------
2
C. Retrieving the file id of a full-text catalog file
This example returns the file ID of a full-text file. The T-SQL code snippet selects the logical file name from the sys.database_files
catalog view, where the file type equals 4
(full-text). This code returns 'NULL' if a full-text catalog does not exist.
SELECT FILE_IDEX((SELECT name FROM sys.master_files WHERE type = 4))
AS 'File_ID';
See Also
Metadata Functions (Transact-SQL)
sys.database_files (Transact-SQL)
sys.master_files (Transact-SQL)