FILE_IDEX (Transact-SQL)
Returns the file identification (ID) number for the specified logical file name of the data, log, or full-text file in the current database.
.gif "Topic link icon")
Syntax
FILE_IDEX (file_name)
Arguments
- file_name
Is an expression of type sysname that represents the name of the file for which to return the file ID.
Return Types
int
NULL on error
Remarks
file_name corresponds to the logical file name displayed in the name column in the sys.master_files or sys.database_files catalog views.
FILE_IDEX can be used in a select list, a WHERE clause, or anywhere an expression is allowed. For more information, see Expressions (Transact-SQL).
Examples
A. Retrieving the file id of a specified file
The following example returns the file ID for the AdventureWorks2008R2_Data file.
USE AdventureWorks2008R2;
GO
SELECT FILE_IDEX('AdventureWorks2008R2_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
The following example returns the file ID of the AdventureWorks2008R2 log file by selecting the logical file name from the sys.database_files catalog view where the file type is equal to 1 (log).
USE AdventureWorks2008R2;
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
The following example returns the file ID of a full-text file by selecting the logical file name from the sys.database_files catalog view where the file type is equal to 4 (full-text). This example will return NULL if a full-text catalog does not exist.
SELECT FILE_IDEX((SELECT name FROM sys.master_files WHERE type = 4))
AS 'File_ID';