SQL Server 依分區表名查詢所屬檔案與檔案群腳本

快速查詢指定分區表對應的分區配置、檔案群與實體檔案路徑：

SELECT 
    ps.name AS PartitionSchemeName,
    dds.destination_id AS PartitionNumber,
    fg.name AS FileGroupName,
    t.name AS TableName,
    f.name AS LogicalFileName,
    f.physical_name AS PhysicalFilePath
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_spaces AS dds ON ps.data_space_id = dds.partition_scheme_id
INNER JOIN sys.filegroups AS fg ON dds.data_space_id = fg.data_space_id
INNER JOIN sys.database_files f ON f.data_space_id = fg.data_space_id
WHERE t.name = 'salesOrder';  -- 替換目標表名

欄位說明

• PartitionSchemeName：分區配置名稱
• PartitionNumber：分區編號（對應 destination_id）
• FileGroupName：檔案群名稱
• LogicalFileName / PhysicalFilePath：邏輯/實體檔案名稱與路徑

適用場景

• 分區表維運：確認分區對齊正確檔案群
• 儲存容量規劃：評估各分區實體檔案分佈
• 遷移/備份驗證：核對檔案群與磁碟對應關係

延伸：查詢非分區表檔案群

SELECT t.name AS TableName, fg.name AS FileGroupName, f.physical_name
FROM sys.tables t
JOIN sys.indexes i ON t.object_id = i.object_id AND i.index_id IN (0,1)
JOIN sys.filegroups fg ON i.data_space_id = fg.data_space_id
JOIN sys.database_files f ON fg.data_space_id = f.data_space_id
WHERE t.name = 'YourTable';