FILEPROPERTY (Transact-SQL)
Applies to: 
SQL Server
Azure SQL Managed Instance
Returns the specified file name property value when a file name in the current database and a property name are specified. Returns NULL for files that are not in the current database.
Transact-SQL syntax conventions
Syntax
FILEPROPERTY ( file_name , property )
Arguments
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 |
Return Types
int
Remarks
file_name corresponds to the name column in the sys.master_files or sys.database_files catalog view.
Examples
The following example returns the setting for the IsPrimaryFile property for the AdventureWorks_Data file name in AdventureWorks2022 the database.
SELECT FILEPROPERTY('AdventureWorks2022_Data', 'IsPrimaryFile')AS [Primary File];
GO
Here's the result set.
Primary File
-------------
1
(1 row(s) affected)
See Also
FILEGROUPPROPERTY (Transact-SQL)
Metadata Functions (Transact-SQL)
sp_spaceused (Transact-SQL)
sys.database_files (Transact-SQL)
sys.master_files (Transact-SQL)