CREATE VIEW Metadata.PartitionValues
AS
SELECT pf.name AS FunctionName,
ps.name AS SchemeName,
pf.function_id AS Functionid,
ps.data_space_id AS DataSpaceId,
rv.Value,
PartitionFilter,
rv.boundary_id AS BoundaryId,
pf.type COLLATE SQL_Latin1_General_CP1_CI_AS AS Type,
pf.type_desc COLLATE SQL_Latin1_General_CP1_CI_AS AS TypeDesc,
pf.boundary_value_on_right AS RangeRight
FROM sys.data_spaces (NOLOCK) ds
INNER JOIN sys.partition_schemes ps (NOLOCK) ON ds.data_space_id = ps.data_space_id
INNER JOIN sys.partition_functions pf (NOLOCK) ON pf.function_id = ps.function_id
INNER JOIN sys.partition_range_values rv (NOLOCK) ON rv.function_id = pf.function_id
INNER JOIN sys.partition_parameters pp (NOLOCK) ON pp.function_id = pf.function_id
INNER JOIN sys.types t (NOLOCK) ON pp.user_type_id = t.user_type_id
LEFT OUTER JOIN sys.partition_range_values rv2 (NOLOCK) ON rv2.function_id = pf.function_id
AND rv2.boundary_id = rv.boundary_id - 1
AND rv.boundary_id > 1
CROSS APPLY (SELECT CASE WHEN t.NAME LIKE '%char%' THEN '''' + REPLACE(CAST(rv.Value AS VARCHAR), '''', '''''') + ''''
WHEN t.NAME LIKE '%date%'
OR t.NAME LIKE '%time%' THEN '''' + CAST(rv.Value AS VARCHAR) + ''''
ELSE CAST(rv.Value AS VARCHAR)
END AS CharValue,
CASE WHEN t.NAME LIKE '%char%' THEN '''' + REPLACE(CAST(rv2.Value AS VARCHAR), '''', '''''') + ''''
WHEN t.NAME LIKE '%date%'
OR t.NAME LIKE '%time%' THEN '''' + CAST(rv2.Value AS VARCHAR) + ''''
ELSE CAST(rv2.Value AS VARCHAR)
END AS PrevCharValue) cv
CROSS APPLY (SELECT CASE WHEN pf.boundary_value_on_right = 0
THEN CASE WHEN rv.boundary_id > 1 THEN '@PartCol > ' + PrevCharValue + ' AND @PartCol <= ' + CharValue
ELSE '@PartCol <= ' + CharValue
END
WHEN pf.boundary_value_on_right = 1
THEN CASE WHEN rv.boundary_id > 1 THEN '@PartCol >= ' + PrevCharValue + ' AND @PartCol < ' + CharValue
ELSE '@PartCol < ' + CharValue
END
END AS PartitionFilter) rf
WHERE pp.parameter_id = 1
AND rv.parameter_id = 1