How to List All physical Files in a logical Filegroup?

In a large SQL Server Database, you may have multiple logical Filegroups. And a Filegroup may have multiple physical Files.

To list all physical Files in a logical Filegroup, you need to join two system tables, sys.filegroups and sys.database_files, using the data_space_id and identifier.

Here is query to count the number of physical Files in each logical Filegroup:

select g.data_space_id, count(*) as count, g.name 
   from sys.filegroups g, sys.database_files f
   where g.data_space_id = f.data_space_id 
   group by g.data_space_id, g.name
   
data_space_id   count   name
1               2       DEFAULT
2               1       PRODUCT
3               4       ORDER

Here is a query to list the physical Files of the first Filegroup:

select g.data_space_id, g.name, F.*
   from sys.filegroups g, sys.database_files f
   where g.data_space_id = f.data_space_id 
      and g.name = 'DEFAULT'

data_space_id  name     file_id  type_desc  name       physical_name          ...
1              DEFAULT  1        ROWS       PRIMARY    D:\DATA\primary.mdf    ...
1              DEFAULT  2        ROWS       EXTENSION  D:\DATA\extension.ndf  ...

⇒ What Is sys.objects

⇐ What Is sys.filegroups?

⇑ SQL Server System Views and Functions

⇑⇑ SQL Server Storage Tutorials