How to Use FILEPROPERTY() in SQL Server

In SQL Server, you can use the FILEPROPERTY() function to return the property value for a specified database file. The returned value is either 1 or 0 (or NULL if the input is invalid).

To use it, provide the file’s logical file name and the property value that you want returned.

Example 1 – Basic Query

Here’s an example to demonstrate.

USE WideWorldImporters;
SELECT FILEPROPERTY('WWI_Primary', 'SpaceUsed') AS Result;

Result:

+----------+
| Result   |
|----------|
| 1152     |
+----------+

The value returned by the SpaceUsed property is the number of pages allocated in the file. Therefore, this example tells us that the WWI_Primary file has 1152 pages allocated.

If I check the log file, I get a different result:

SELECT FILEPROPERTY('WWI_Log', 'SpaceUsed') AS Result;

Result:

+----------+
| Result   |
|----------|
| 14762    |
+----------+

Example 2 – Getting the File’s Name from its ID

If you’re not sure of the file name, but you know its ID, you can use the FILE_NAME() function to return the file’s name based on its ID.

SELECT FILEPROPERTY(FILE_NAME(3), 'SpaceUsed') AS Result;

Result:

+----------+
| Result   |
|----------|
| 52872    |
+----------+

Here it is again with the file name also returned:

SELECT 
  FILE_NAME(3) AS [File Name],
  FILEPROPERTY(FILE_NAME(3), 'SpaceUsed') AS [Space Used];

Result:

+--------------+--------------+
| File Name    | Space Used   |
|--------------+--------------|
| WWI_UserData | 52872        |
+--------------+--------------+

Example 3 – Return All Property Values

At the time of writing, FILEPROPERTY() accepts four property values.

Here’s an example that returns all four property values for the WWI_Log file.

DECLARE @file_name varchar(50) = 'WWI_Log';
SELECT 
  FILEPROPERTY(@file_name, 'IsReadOnly') AS IsReadOnly,
  FILEPROPERTY(@file_name, 'IsPrimaryFile') AS IsPrimaryFile,
  FILEPROPERTY(@file_name, 'IsLogFile') AS IsLogFile,
  FILEPROPERTY(@file_name, 'SpaceUsed') AS SpaceUsed;

Result:

+--------------+-----------------+-------------+-------------+
| IsReadOnly   | IsPrimaryFile   | IsLogFile   | SpaceUsed   |
|--------------+-----------------+-------------+-------------|
| 0            | 0               | 1           | 14763       |
+--------------+-----------------+-------------+-------------+

Example 4 – Non-Existent File

Here’s what happens if you specify a file that doesn’t exist.

SELECT FILEPROPERTY('OOPS', 'SpaceUsed') AS Result;

Result:

+----------+
| Result   |
|----------|
| NULL     |
+----------+

Example 5 – Invalid Property Value

We get the same result when the file is correct, but we specify an invalid property value.

SELECT FILEPROPERTY('WWI_Log', 'Oops') AS Result;

Result:

+----------+
| Result   |
|----------|
| NULL     |
+----------+