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

To use it, provide the filegroup name and the property value that you want returned.

Example 1 – Basic Query

Here’s an example to demonstrate.

USE WideWorldImporters;
SELECT FILEGROUPPROPERTY('PRIMARY', 'IsDefault') AS Result;

Result:

+----------+
| Result   |
|----------|
| 0        |
+----------+

The value of 0 means that this is not the default filegroup for this database.

If I switch databases, I can see that the PRIMARY filegroup is the default filegroup for that database:

USE Music;
SELECT FILEGROUPPROPERTY('PRIMARY', 'IsDefault') AS Result;

Result:

Changed database context to 'Music'.
+----------+
| Result   |
|----------|
| 1        |
+----------+

Example 2 – Return All Property Values

At the time of writing, FILEGROUPPROPERTY() accepts three property values.

Here’s an example that returns all three property values for the USERDATA filegroup.

SELECT 
  FILEGROUPPROPERTY('USERDATA', 'IsReadOnly') AS FG_1,
  FILEGROUPPROPERTY('USERDATA', 'IsUserDefinedFG') AS FG_2,
  FILEGROUPPROPERTY('USERDATA', 'IsDefault') AS FG_3;

Result:

+--------+--------+--------+
| FG_1   | FG_2   | FG_3   |
|--------+--------+--------|
| 0      | 1      | 1      |
+--------+--------+--------+

Example 3 – Non-Existent Filegroup

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

SELECT FILEGROUPPROPERTY('OOPS', 'IsDefault') AS Result;

Result:

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

Example 4 – Invalid Property Value

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

SELECT FILEGROUPPROPERTY('USERDATA', 'OOPS') AS Result;

Result:

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

Return Filegroup Name

If you have the filegroup’s ID but not its name, you can use FILEGROUP_NAME() to return a filegroup’s name based on its ID.

Return All Filegroups

If you want to return all filegroups for a given database, see How to Return All Filegroups in SQL Server.

Create a Filegroup

If you want to create a new filegroup, see How to Add a Filegroup in SQL Server.