how to generate the creation script for partition function and partition schema?

I have a script to generate the create table script. You can see it on my answer to this question.

However, now I need to script my partition functions and partition schemes

On this question here, see the answer has examples on how to add a partition to a table and how to remove a partition from a table, and I would like to have a look at the scripts at any stage.

I have found this link: how to find partition function text applied to a table

how to generate the scripts for create partition function and partition schema?

I was working on a script to generate the create partition function
as you can see below, but it only works for my own PF_year partition function
because it was tricky to get over the fact that sys.partition_range_values has a sql_variant data type as value.

when I used a case I got the following error:


Msg 206, Level 16, State 2, Line 35


Operand type clash: int is incompatible with date

Then in his great answer Dan Guzman, showed us SQL_VARIANT_PROPERTY some wonderful thing that I was not aware of, now I am a new DBA and person.

```
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SELECT
RADHE = 'CREATE PARTITION FUNCTION' + space(1) + quotename(spf.name) +
'(' +
COALESCE(baset.name,'data type not found') + ')' + CHAR(13) +
'AS RANGE ' + CASE WHEN CAST(spf.boundary_value_on_right AS int) = 1
THEN 'RIGHT ' ELSE 'LEFT ' END + CHAR(13) +
'FOR VALUES (' +
(SELECT STUFF(
(SELECT
[text()] = N',''' + CAST(
CASE st.system_type_id
WHEN 40 THEN CONVERT (date, sprv.value,126)
--WHEN 56 THEN CONVERT (int, sprv.value)
--ELSE CONVERT (int, sprv.value)
END
AS NVARCHAR) + ''''
FROM sys.partition_range_values sprv
WHERE sprv.function_id=spf.function_id
order by sprv.boundary_id
FOR XML PATH('') ), 1, 1,N'')+ N');')

,st.system_type_id
,s

I see from your comment on Terry C's answer that you are looking for a T-SQL solution to script partition functions and schemes. Although I think SMO or SSDT (via SSMS, Powershell, Visual Studio) is the right tool for scripting DDL, it is possible accomplish via some ugly T-SQL. Below are sample scripts for this particular task.

SELECT
      N'CREATE PARTITION FUNCTION ' 
    + QUOTENAME(pf.name)
    + N'(' + t.name  + N')'
    + N' AS RANGE ' 
    + CASE WHEN pf.boundary_value_on_right = 1 THEN N'RIGHT' ELSE N'LEFT' END
    + ' FOR VALUES('
    +
    (SELECT
        STUFF((SELECT
            N','
            + CASE
                  WHEN SQL_VARIANT_PROPERTY(r.value, 'BaseType') IN(N'char', N'varchar') 
                    THEN QUOTENAME(CAST(r.value AS nvarchar(4000)), '''')
                  WHEN SQL_VARIANT_PROPERTY(r.value, 'BaseType') IN(N'nchar', N'nvarchar') 
                    THEN N'N' + QUOTENAME(CAST(r.value AS nvarchar(4000)), '''')
                  WHEN SQL_VARIANT_PROPERTY(r.value, 'BaseType') = N'date' 
                    THEN QUOTENAME(FORMAT(CAST(r.value AS date), 'yyyy-MM-dd'),'''')
                  WHEN SQL_VARIANT_PROPERTY(r.value, 'BaseType') = N'datetime' 
                    THEN QUOTENAME(FORMAT(CAST(r.value AS datetime), 'yyyy-MM-ddTHH:mm:ss'),'''')
                  WHEN SQL_VARIANT_PROPERTY(r.value, 'BaseType') IN(N'datetime', N'smalldatetime') 
                    THEN QUOTENAME(FORMAT(CAST(r.value AS datetime), 'yyyy-MM-ddTHH:mm:ss.fff'),'''')
                  WHEN SQL_VARIANT_PROPERTY(r.value, 'BaseType') = N'datetime2' 
                    THEN QUOTENAME(FORMAT(CAST(r.value AS datetime2), 'yyyy-MM-ddTHH:mm:ss.fffffff'),'''')
                  WHEN SQL_VARIANT_PROPERTY(r.value, 'BaseType') = N'datetimeoffset' 
                    THEN QUOTENAME(FORMAT(CAST(r.value AS datetimeoffset), 'yyyy-MM-dd HH:mm:ss.fffffff K'),'''')
                  WHEN SQL_VARIANT_PROPERTY(r.value, 'BaseType') = N'time' 
                    THEN QUOTENAME(FORMAT(CAST(r.value AS time), 'hh\:mm\:ss\.fffffff'),'''') --'HH\:mm\:ss\.fffffff'
                  WHEN SQL_VARIANT_PROPERTY(r.value, 'BaseType') = N'uniqueidentifier' 
                    THEN QUOTENAME(CAST(r.value AS nvarchar(4000)), '''')
                  WHEN SQL_VARIANT_PROPERTY(r.value, 'BaseType') IN (N'binary', N'varbinary') 
                    THEN CONVERT(nvarchar(4000), r.value, 1)
                  ELSE CAST(r.value AS nvarchar(4000))
              END
    FROM sys.partition_range_values AS r
    WHERE pf.[function_id] = r.[function_id]
    FOR XML PATH(''), TYPE).value('.', 'nvarchar(MAX)'),1,1,N'')
    )
    + N');'
FROM sys.partition_functions pf
JOIN sys.partition_parameters AS pp ON
    pp.function_id = pf.function_id
JOIN sys.types AS t ON
    t.system_type_id = pp.system_type_id
    AND t.user_type_id = pp.user_type_id
WHERE pf.name = N'PF_Year';

SELECT
      N'CREATE PARTITION SCHEME ' + QUOTENAME(ps.name)
    + N' AS PARTITION ' + QUOTENAME(pf.name)
    + N' TO ('
    +
    (SELECT
        STUFF((SELECT
            N',' + QUOTENAME(fg.name)
    FROM sys.data_spaces ds
    JOIN sys.destination_data_spaces AS dds ON dds.partition_scheme_id = ps.data_space_id
    JOIN sys.filegroups AS fg ON fg.data_space_id = dds.data_space_id
    WHERE ps.data_space_id = ds.data_space_id
    ORDER BY dds.destination_id
    FOR XML PATH(''), TYPE).value('.', 'nvarchar(MAX)'),1,1,N'')
    )
    + N');'
FROM sys.partition_schemes AS ps
JOIN sys.partition_functions AS pf ON pf.function_id = ps.function_id
WHERE ps.name = N'PSC_Year';

StackExchange Database Administrators Q#171365, answer score: 10