CREATE VIEW Metadata.PartitionFunctions
AS
WITH part
AS (SELECT pf.name COLLATE SQL_Latin1_General_CP1_CI_AS AS PartitionFunction,
pf.fanOut AS ValCnt,
pf.boundary_value_on_right AS IsRightBoundary,
ct.ColumnType AS ParameterType,
pf.create_date AS CreateDate,
pf.modify_date AS ModifyDate,
pf.function_id AS FunctionId,
'DROP PARTITION FUNCTION [' + pf.NAME COLLATE SQL_Latin1_General_CP1_CI_AS + ']' AS DropScript,
'CREATE PARTITION FUNCTION [' + pf.name COLLATE SQL_Latin1_General_CP1_CI_AS + '](' + ct.ColumnType + ') AS RANGE '
+ CASE WHEN boundary_value_on_right = 1 THEN 'RIGHT'
ELSE 'LEFT'
END + ' FOR VALUES(' + Util.dbo.StringConcat(CASE WHEN y.name LIKE '%char'
OR y.name = 'sysname' THEN '''' + CAST(rv.VALUE AS VARCHAR(4000)) + ''''
WHEN y.name LIKE '%date%'
OR y.name LIKE '%time%' THEN '''' + CAST(rv.VALUE AS VARCHAR) + ''''
WHEN y.collation_name IS NULL
AND y.PRECISION > 0
AND y.name NOT LIKE '%date%'
AND y.name NOT LIKE '%time%' THEN CAST(rv.VALUE AS VARCHAR)
END, ', ') + ')' AS CreateScript
FROM sys.partition_functions pf (NOLOCK)
INNER JOIN sys.partition_parameters pp (NOLOCK) ON pf.function_id = pp.function_id
AND pp.parameter_id = 1
INNER JOIN sys.types y (NOLOCK) ON y.user_type_id = pp.user_type_id
INNER JOIN sys.databases db (NOLOCK) ON db.database_id = DB_ID()
CROSS APPLY Util.dbo.GetColumnType(y.name, pp.max_length, pp.PRECISION, pp.scale, pp.collation_name, db.collation_name) ct
INNER JOIN sys.partition_range_values rv (NOLOCK) ON rv.function_id = pf.function_id
AND RV.parameter_id = 1
GROUP BY pf.name COLLATE SQL_Latin1_General_CP1_CI_AS,
pf.fanOut,
pf.boundary_value_on_right,
ct.ColumnType,
pf.function_id,
pf.create_date,
pf.modify_date)
SELECT PartitionFunction,
ValCnt,
IsRightBoundary,
ParameterType,
CreateDate,
ModifyDate,
FunctionId,
DropScript,
CreateScript
FROM part