FILEPROPERTY (Transact-SQL)

file_name

Is an expression that contains the name of the file associated with the current database for which to return property information. file_name is nchar(128).

property

Is an expression that contains the name of the file property to return. property is varchar(128), and can be one of the following values.

Value	Description	Value returned
IsReadOnly	Filegroup is read-only.	1 = True 0 = False NULL = Input is not valid.
IsPrimaryFile	File is the primary file.	1 = True 0 = False NULL = Input is not valid.
IsLogFile	File is a log file.	1 = True 0 = False NULL = Input is not valid.
SpaceUsed	Amount of space that is used by the specified file.	Number of pages allocated in the file

int

file_name corresponds to the name column in the sys.master_files or sys.database_files catalog view.

The following example returns the setting for the IsPrimaryFile property for the AdventureWorks2008R2_Data file name in the AdventureWorks2008R2 database.

USE AdventureWorks2008R2;
GO
SELECT FILEPROPERTY('AdventureWorks2008R2_Data', 'IsPrimaryFile')AS [Primary File];
GO

Here is the result set.

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

Reference