Find the file and file group based on the partition table name

Select ps. Name
As psname,

DDS. destination_idas partitionnumber,

FG. Name as filegroupname, FG. Name,

T. Name,

F. Name
Filename

From (SYS. Tables
As T

Inner join
SYS. Indexes
As I

On (T. object_id
= I. object_id ))

Inner join
SYS. partition_schemes
As PS

On (I. data_space_id
= Ps. data_space_id ))

Inner join
SYS. destination_data_spacesas DDS

On (PS. data_space_id
= DDS. partition_scheme_id ))

Inner join
SYS. filegroups
As FG

Ondds. data_space_id
= FG. data_space_id

Innerjoin
SYS. database_files F
On F. data_space_id
= FG. data_space_id

Where T. Name = 'salesorder'