如何在SQL Server中一次性查询分区架构、文件组、文本图像及表注释？

针对你创建的分区表和指定文件组的表，以下SQL语句可以一次性检索到表架构、表名、分区架构（含分区列）、数据存储文件组、文本图像存储文件组以及表注释：

SELECT
    SCHEMA_NAME(t.schema_id) AS 表架构,
    t.name AS 表名,
    ps.name AS 分区架构名,
    STRING_AGG(c.name, ', ') AS 分区列,
    fg_data.name AS 数据文件组,
    fg_text.name AS 文本图像文件组,
    ep.value AS 表注释
FROM
    sys.tables t
LEFT JOIN sys.indexes i 
    ON t.object_id = i.object_id 
    AND i.index_id IN (0, 1) -- 匹配堆或聚集索引，对应表的核心存储信息
LEFT JOIN sys.partition_schemes ps 
    ON i.data_space_id = ps.data_space_id
LEFT JOIN sys.index_columns ic 
    ON i.object_id = ic.object_id 
    AND i.index_id = ic.index_id 
    AND ic.partition_ordinal > 0 -- 仅筛选分区列
LEFT JOIN sys.columns c 
    ON ic.object_id = c.object_id 
    AND ic.column_id = c.column_id
LEFT JOIN sys.filegroups fg_data 
    ON i.data_space_id = fg_data.data_space_id
LEFT JOIN sys.filegroups fg_text 
    ON t.lob_data_space_id = fg_text.data_space_id
LEFT JOIN sys.extended_properties ep 
    ON t.object_id = ep.major_id 
    AND ep.minor_id = 0 
    AND ep.class = 1 
    AND ep.name = 'MS_Description'
GROUP BY
    SCHEMA_NAME(t.schema_id),
    t.name,
    ps.name,
    fg_data.name,
    fg_text.name,
    ep.value
ORDER BY
    表架构, 表名;

关键逻辑说明： #

• 关联sys.indexes获取表的存储根信息（堆或聚集索引决定了数据的实际存储位置）
• 通过sys.partition_schemes识别分区表，再联动sys.index_columns和sys.columns提取分区列
• 用t.lob_data_space_id关联文件组，获取大对象字段（如VARCHAR(MAX)）的专属存储位置
• 从sys.extended_properties中读取表的MS_Description扩展注释
• 用STRING_AGG聚合多分区列（如果表存在多个分区列的情况）

执行该查询后，两张测试表的信息会完整返回：

• TB_PARTITION_SCHEMA：显示分区架构名、分区列COL，数据文件组对应分区架构关联的文件组，文本图像文件组为NULL（无大对象列），以及对应的表注释
• TB_FILEGROUP：显示分区架构名为NULL（非分区表），分区列为NULL，数据文件组test1fg，文本图像文件组test2fg，以及对应的表注释

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