file-idex-transact-sql.md

title	FILE_IDEX (Transact-SQL) | Microsoft Docs
ms.custom
ms.date	03/03/2017
ms.prod	sql-non-specified
ms.reviewer
ms.suite
ms.technology	database-engine
ms.tgt_pltfrm
ms.topic	language-reference
f1_keywords	FILE_IDEX FILE_IDEX_TSQL
dev_langs	TSQL
helpviewer_keywords	FILE_IDEX function IDs [SQL Server], files file IDs [SQL Server] names [SQL Server], files identification numbers [SQL Server], files file names [SQL Server], FILE_IDEX
ms.assetid	7532fea5-ee5e-4edd-b98b-111a7ba56c8e
caps.latest.revision	35
author	BYHAM
ms.author	rickbyh
manager	jhubbard

FILE_IDEX (Transact-SQL)

[!INCLUDEtsql-appliesto-ss2008-xxxx-xxxx-xxx_md]

Returns the file identification (ID) number for the specified logical file name of the data, log, or full-text file in the current database.

Transact-SQL Syntax Conventions

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 AdventureWorks_Data file.

USE AdventureWorks2012;  
GO  
SELECT FILE_IDEX('AdventureWorks2012_Data')AS 'File ID';  
GO  

[!INCLUDEssResult]

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 AdventureWorks 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 AdventureWorks2012;  
GO  
SELECT FILE_IDEX((SELECT TOP(1)name FROM sys.database_files   
WHERE type = 1))AS 'File ID';  
GO  

[!INCLUDEssResult]

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';  

See Also

Metadata Functions (Transact-SQL)
sys.database_files (Transact-SQL)
sys.master_files (Transact-SQL)