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.

    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 
    'FOR VALUES (' +
    [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)
                      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');')

    ,spf.name AS [Name],
    spf.function_id AS [ID],
    CAST(spf.boundary_value_on_right AS int) AS [RangeType],
    spf.create_date AS [CreateDate],
    spf.fanout AS [NumberOfPartitions]
    sys.partition_functions AS spf

    INNER JOIN sys.partition_parameters AS spp 
            ON spp.function_id=spf.function_id

    INNER JOIN sys.types AS st 
            ON st.system_type_id = st.user_type_id 
           and spp.system_type_id = st.system_type_id

    LEFT OUTER JOIN sys.types AS baset 
                  ON (     baset.user_type_id = spp.system_type_id 
                       and baset.user_type_id = baset.system_type_id) 

                  or (     (baset.system_type_id = spp.system_type_id) 
                       and (baset.user_type_id = spp.user_type_id) 
                       and (baset.is_user_defined = 0) 
                       and (baset.is_assembly_type = 1))
    WHERE spf.name = 'PF_YEAR';

2 Answers 2


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.

    + 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('
            + 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))
    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';

    + N' AS PARTITION ' + QUOTENAME(pf.name)
    + N' TO ('
            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';
  • Typo in script: PARTTITION --> PARTITION
    – Marek P
    Commented Apr 4 at 15:05
  • @MarekP, corrected. Thanks for pointing out the typo.
    – Dan Guzman
    Commented Apr 4 at 15:32

please try these steps:

  1. Using SSMS - Check your Scripting Options.
    Go to Tools/Options menu. Navigate to 'SQL Server Object Explorer' / 'Scripting' Check 'Script Partition Schemes' is set to True. (False appears to be default) Also worth checking setting for 'Script file groups'

  2. In Object Explorer - right click your database name, and then select Tasks / 'Generate Scripts' for all database objects. You should see partition schemes early in the scripted text.

  • thanks for your answer but we want something T-SQL, I should have mentioned in the question Commented Apr 19, 2017 at 9:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.