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_descFROM 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_descFROM 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 | PRIMARYdata_space_id | 1type | FGtype_desc | ROWS_FILEGROUPis_default | 1is_system | 0filegroup_guid | NULLlog_filegroup_id | NULLis_read_only | 0is_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.
SELECTdf.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 dfINNER JOIN sys.filegroups AS fgON df.data_space_id = fg.data_space_id;
Result (using vertical output):
DB File Name | MusicFile Size (MB) | 8File Group Name | PRIMARYFile 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.
