1. 引言

    本文将介绍如何查询Sales模式下Orders分区表关联的分区函数和分区方案

2. 查询分区表的关联信息

step one：查询分区方案

SELECT 
    t.name AS Table_Name,
    s.name AS Schema_Name,
    i.name AS Index_Name,
    ps.name AS Partition_Scheme_Name
FROM 
    sys.tables t
JOIN 
    sys.schemas s ON t.schema_id = s.schema_id
JOIN 
    sys.indexes i ON t.object_id = i.object_id
JOIN 
    sys.partition_schemes ps ON i.data_space_id = ps.data_space_id
WHERE 
    s.name = 'Sales' 
    AND t.name = 'Orders';

step two：查询分区函数

SELECT 
    ps.name AS Partition_Scheme_Name,
    pf.name AS Partition_Function_Name
FROM 
    sys.partition_schemes ps
JOIN 
    sys.partition_functions pf ON ps.function_id = pf.function_id
WHERE 
    ps.name IN (
        SELECT 
            ps.name
        FROM 
            sys.tables t
        JOIN 
            sys.schemas s ON t.schema_id = s.schema_id
        JOIN 
            sys.indexes i ON t.object_id = i.object_id
        JOIN 
            sys.partition_schemes ps ON i.data_space_id = ps.data_space_id
        WHERE 
            s.name = 'Sales' 
            AND t.name = 'Orders'
    );

step three：查询分区函数的边界值

SELECT 
    pf.name AS Partition_Function_Name,
    pf.boundary_value_on_right,
    pf.fanout,
    prv.value AS Boundary_Value,
    prv.position
FROM 
    sys.partition_functions pf
LEFT JOIN 
    sys.partition_range_values prv ON pf.function_id = prv.function_id
WHERE 
    pf.name IN (
        SELECT 
            pf.name
        FROM 
            sys.partition_schemes ps
        JOIN 
            sys.partition_functions pf ON ps.function_id = pf.function_id
        WHERE 
            ps.name IN (
                SELECT 
                    ps.name
                FROM 
                    sys.tables t
                JOIN 
                    sys.schemas s ON t.schema_id = s.schema_id
                JOIN 
                    sys.indexes i ON t.object_id = i.object_id
                JOIN 
                    sys.partition_schemes ps ON i.data_space_id = ps.data_space_id
                WHERE 
                    s.name = 'Sales' 
                    AND t.name = 'Orders'
            )
    )
ORDER BY 
    pf.name, prv.position;