3

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
    ,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]
    FROM
    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';
9

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 PARTTITION ' + 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';
5

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 – marcello miorelli Apr 19 '17 at 9:37
  • @marcellomiorelli this will work for scheme. I tested it out. – SqlWorldWide Apr 19 '17 at 12:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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