The SQL Server system function, FILEPROPERTY(), is used to retrieve database file properties. I will show you use cases of this system function to understand how to use it to retrieve the properties of any database files.

The syntax of this function FILEPROPERTY() is given below as per the MSDN documentation.

FILEPROPERTY ( file_name , property )

Here, you need to specify the database file name in place of the file_name argument and the property name that you want to retrieve in place of the property argument. I have listed all the properties that can be retrieved using this function below:

• IsReadOnly
• IsPrimaryFile
• IsLogFile
• SpaceUsed

Use Cases

I will take a user database, named dbstate, for this demonstration. Let’s execute the query below to get the names of all the database files. You can either launch SQL Server Management Studio or SQL tools to execute all these queries.

I have returned this information from a system object sys.database_files. You can also use the sp_helpdb system stored procedure to get similar details.

USE dbstate
GO
SELECT name, 
       physical_name, 
       state_desc,
       type_desc 
FROM sys.database_files

I have executed the above script to get the database details, and we can see the database file names and their status in the image below.

Now, we will take any file name from the above output to run the system function FILEPORPERTY. To get their respective properties, you can also specify all file names in separate statements. I have specified the primary database file name, dbstate, and returned the IsPrimaryFile property status to check whether this file is a primary database file or not. This property will return 1 if the specified file is a primary database file, 0 if the specified file is not a primary file, and NULL if the specified file is not associated with the database name under which you are running this query.

I have used the below query to get this output.

USE dbstate
GO
SELECT FILEPROPERTY('dbstate', 'IsPrimaryFile') AS [Primary File]

We can see the result in the below image which has returned as 1. It means the specified file is the primary database file of database dbstate.

We will showcase another use case in which I will return another property value to check whether the specified database file is a log file. I have taken 2 database files from the first image or output. One is the primary file dbstate and the second one is the transaction log file dbstate_log. Then we will check one of the properties IsLogFile to find whether any of the specified database files are log file. The property IsLogFile returns 1 if the specified file is the log file and 0 if the specified file is not the log file. We will get a NULL value if the specified file is wrong or is not associated with the database in which we are executing the query.

You can replace your database file names in the below query if you want to check it for your desired database.

USE dbstate
GO
SELECT FILEPROPERTY('dbstate', 'IsLogFile') AS [Log File],
       FILEPROPERTY('dbstate_log', 'IsLogFile') AS [Log File]

I have executed the above query to check which database file is a log file, and I got this answer by looking into its output. We got that the dbstate_log file has returned the output as 1, so this file will be the log file of the database dbstate.

The next use case of this system function will talk about the space consumed by the specified file. This time I have taken another database file dbstate_secondary and returned its used space by specifying the SpaceUsed property.

USE dbstate
GO
SELECT FILEPROPERTY('dbstate_secondary', 'SpaceUsed') AS [Space Used]

Look at the output of the above query showing that the specified file has consumed 8 pages in its file. Similarly, we can also return the result of any database file whether that specified file is part of the read-only filegroup by using another property that is known as IsReadOnly. This property will also return 1 if the specified database file is part of the read-only file group, whereas you will get 0 if this condition is false.

The next use case will demonstrate specifying the wrong database file name and then the result. You can see database dbstate has 3 database files named dbstate, dbstate_secondary, and dbstate_log. I will specify another file name, which is dbstatedata, to return its used space property.

USE dbstate
GO
SELECT FILEPROPERTY('dbstatedata', 'SpaceUsed') AS [Space Used]

The above query has returned the output as NULL. It means if you specify the wrong database file names or a database name that is not there in your current database under which you are running the query, then the output will be returned as NULL.

Conclusion

Today I have explained a system function FILEPROPERTY and some of the use cases in this article. You can use this system function for your various business requirements. Please share your feedback in the comment section, or you can also drop your queries or questions there.