Export (0) Print
Expand All

FILE_IDEX (Transact-SQL)

 

Applies To: SQL Server 2014, SQL Server 2016 Preview

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

Applies to: SQL Server (SQL Server 2008 through current version).

Topic link icon Transact-SQL Syntax Conventions


FILE_IDEX ( file_name )

file_name

Is an expression of type sysname that represents the name of the file for which to return the file ID.

int

NULL on error

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).

The following example returns the file ID for the AdventureWorks_Data file.

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

Here is the result set.

File ID 
------- 
1
(1 row(s) affected)

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

Here is the result set.

File ID 
------- 
2

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';
Was this page helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

ADD
Show:
© 2015 Microsoft