In SQL Server, you can query the sys.filegroups system catalog view to return a list of all filegroups for the current database.

This view contains a row for each data space that is a filegroup. In other words, your results contain one row per filegroup.

Example 1 – Using the WideWorldImporters Database

Here’s an example to demonstrate. This example uses the WideWorldImporters sample database provided by Microsoft.

USE WideWorldImporters;
SELECT 
data_space_id,
name,
type_desc
FROM sys.filegroups;

Result:

+-----------------+-------------------+---------------------------------+
| data_space_id   | name              | type_desc                       |
|-----------------+-------------------+---------------------------------|
| 1               | PRIMARY           | ROWS_FILEGROUP                  |
| 2               | USERDATA          | ROWS_FILEGROUP                  |
| 3               | WWI_InMemory_Data | MEMORY_OPTIMIZED_DATA_FILEGROUP |
+-----------------+-------------------+---------------------------------+

Example 2 – Switch Databases

In this example, I switch to a different database and run the query again.

USE Music;
SELECT 
data_space_id,
name,
type_desc
FROM sys.filegroups;

Result:

+-----------------+---------+----------------+
| data_space_id   | name    | type_desc      |
|-----------------+---------+----------------|
| 1               | PRIMARY | ROWS_FILEGROUP |
+-----------------+---------+----------------+

This database (called Music) only has one filegroup.

Example 3 – Return All Columns

In the previous examples, I selected specific columns to return. In this example, I use an asterisk (*) to return them all.

SELECT * FROM sys.filegroups;

Result (using vertical output):

name                  | PRIMARY
data_space_id         | 1
type                  | FG
type_desc             | ROWS_FILEGROUP
is_default            | 1
is_system             | 0
filegroup_guid        | NULL
log_filegroup_id      | NULL
is_read_only          | 0
is_autogrow_all_files | 0

In this case I used vertical output to display the results vertically (so that you aren’t forced to scroll horizontally).

Example 4 – Include the File Path

You can join the sys.database_files view to return the physical file path.

SELECT
df.name AS [DB File Name],
df.size/128 AS [File Size (MB)],
fg.name AS [File Group Name],
df.physical_name AS [File Path]
FROM sys.database_files AS df
INNER JOIN sys.filegroups AS fg
ON df.data_space_id = fg.data_space_id;

Result (using vertical output):

DB File Name    | Music
File Size (MB)  | 8
File Group Name | PRIMARY
File Path       | /var/opt/mssql/data/Music.mdf

The PRIMARY filegroup is the default filegroup that the data file and log file reside, unless you specify a different filegroup. See How to Add a Filegroup to a SQL Server Database to create a new filegroup.