CREATE VIEW Metadata.PartitionSchemes
AS
WITH s1
AS (SELECT ps.NAME COLLATE SQL_Latin1_General_CP1_CI_AS AS PartitionScheme,
pf.name COLLATE SQL_Latin1_General_CP1_CI_AS AS PartitionFunction,
pf.fanOut AS ValCnt,
ps.data_space_id,
ps.is_default AS IsDefault,
ps.function_id AS FunctionId,
'DROP PARTITION SCHEME [' + ps.name COLLATE SQL_Latin1_General_CP1_CI_AS + ']' AS DropScript,
'CREATE PARTITION SCHEME [' + ps.name COLLATE SQL_Latin1_General_CP1_CI_AS + '] AS PARTITION [' + pf.NAME COLLATE SQL_Latin1_General_CP1_CI_AS
+ ']' + CASE WHEN COUNT(DISTINCT dd.data_space_id) = 1 THEN ' ALL TO ([' + MAX(ds.NAME COLLATE SQL_Latin1_General_CP1_CI_AS) + '])'
ELSE ' TO (' + Util.dbo.StringConcat(QUOTENAME(DS.NAME COLLATE SQL_Latin1_General_CP1_CI_AS), ', ') + ')'
END AS CreateScript
FROM sys.partition_schemes ps (NOLOCK)
INNER JOIN sys.partition_functions pf (NOLOCK) ON pf.function_id = ps.function_id
INNER JOIN sys.destination_data_spaces dd (NOLOCK) ON dd.partition_scheme_id = ps.data_space_id
INNER JOIN sys.data_spaces ds (NOLOCK) ON ds.data_space_id = dd.data_space_id
GROUP BY ps.name COLLATE SQL_Latin1_General_CP1_CI_AS,
pf.name COLLATE SQL_Latin1_General_CP1_CI_AS,
pf.fanOut,
ps.name COLLATE SQL_Latin1_General_CP1_CI_AS,
ps.data_space_id,
ps.is_default,
ps.function_id)
SELECT PartitionScheme,
PartitionFunction,
ValCnt,
data_space_id,
IsDefault,
FunctionId,
DropScript,
CreateScript,
(SELECT 1 AS tag, NULL AS parent, CHAR (13) + CreateScript + CHAR (13) AS [Q!1!Q!CDATA]
FOR
XML EXPLICIT,
TYPE) AS CreateScriptXML
FROM s1