如何通过编程方式查找SQL Server中无索引堆表所属的文件组

解决方案 #

你之前找到的查询无法匹配无索引表，是因为无索引的堆表在sys.indexes系统视图中存在一条index_id = 0的固定记录，只需要调整查询逻辑即可，以下两个方案均适配Microsoft SQL Server 2016 (SP1-GDR)版本：

方案1：修改原有查询适配堆表场景

SELECT 
    o.name AS 表名,
    f.name AS 所属文件组名
FROM sys.indexes i
INNER JOIN sys.filegroups f 
    ON i.data_space_id = f.data_space_id
INNER JOIN sys.all_objects o 
    ON i.object_id = o.object_id
WHERE 
    o.name = 'My_Table' -- 替换为实际表名
    AND o.type = 'U' -- 仅筛选用户表
    AND i.index_id IN (0,1) -- 0对应无索引堆表，1对应有聚集索引的表
GO

方案2：通用表文件组查询（不受索引状态影响）

SELECT 
    t.name AS 表名,
    ds.name AS 所属文件组名
FROM sys.tables t
INNER JOIN sys.indexes i 
    ON t.object_id = i.object_id AND i.index_id IN (0,1)
INNER JOIN sys.data_spaces ds 
    ON i.data_space_id = ds.data_space_id
WHERE t.name = 'My_Table' -- 替换为实际表名
GO

注意事项 #

如果你的表是分区表，需要额外关联sys.partition_schemes视图查询各分区对应的文件组，普通非分区表直接使用上述代码即可返回正确结果。

内容的提问来源于stack exchange，提问作者Akshaya Sakthivel