SQL Server中如何查询分区架构、分区列及TEXTIMAGE相关信息？

以下是可一次性查询你创建的四类表，同时返回分区架构、分区列、TEXTIMAGE指定存储信息的SQL语句：

SELECT
    t.name AS table_name,
    -- 分区架构名称
    ps.name AS partition_schema_name,
    -- 分区列（多列时用逗号分隔）
    STRING_AGG(c.name, ', ') AS partition_columns,
    -- 主数据文件组
    fg.name AS primary_filegroup,
    -- TEXTIMAGE类型数据存储的文件组
    tifg.name AS text_image_filegroup,
    -- 是否为分区表标记
    CASE WHEN ps.name IS NOT NULL THEN '是' ELSE '否' END AS is_partitioned
FROM sys.tables t
LEFT JOIN sys.indexes i 
    ON t.object_id = i.object_id 
    AND i.type <= 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 
    ON t.data_space_id = fg.data_space_id
LEFT JOIN sys.filegroups tifg 
    ON t.text_image_filegroup_id = tifg.data_space_id
WHERE t.name IN ('TB_PARTITION_SCHEMA', 'TB_ONLYFG', 'TB_TEXTIMAGE', 'TB_FILEGROUP')
GROUP BY t.name, ps.name, fg.name, tifg.name, t.text_image_filegroup_id, ps.data_space_id
ORDER BY t.name;

关键字段说明： #

• partition_schema_name：返回表绑定的分区架构名，非分区表显示NULL
• partition_columns：列出表的分区列（多列时自动拼接），非分区表显示NULL
• text_image_filegroup：显示TEXTIMAGE类型数据指定存储的文件组，未指定则显示NULL
• is_partitioned：直观标记该表是否为分区表

兼容低版本SQL Server说明： #

若使用SQL Server 2017以下版本，STRING_AGG函数不可用，可将分区列查询替换为以下写法：

STUFF(
    (SELECT ', ' + c.name 
     FROM sys.index_columns ic 
     JOIN sys.columns c ON ic.object_id = c.object_id AND ic.column_id = c.column_id
     WHERE ic.object_id = t.object_id AND ic.index_id = i.index_id AND ic.partition_ordinal >0
     FOR XML PATH('')), 1, 2, ''
) AS partition_columns

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