如何生成分區函式和分區模式的創建腳本？

我有一個腳本來生成創建表腳本。你可以在我對這個問題的回答中看到它。

但是，現在我需要編寫分區函式和分區方案的腳本

關於這個問題here，請參閱答案，其中包含有關如何將分區添加到表以及如何從表中刪除分區的範例，我想看看任何階段的腳本。

我找到了這個連結：how to find partition function text applied to a table

如何生成創建分區函式和分區模式的腳本？

我正在編寫一個腳本來生成create partition function 如下所示的，但它僅適用於我自己的PF_year分區函式，因為很難克服sys.partition_range_values具有sql_variant數據類型作為值was的事實。

當我使用案例時，出現以下錯誤：

消息 206，第 16 級，狀態 2，第 35 行

操作數類型沖突：int 與日期不兼容

然後在他的精彩回答Dan Guzman中，向我們展示了SQL_VARIANT_PROPERTY一些我不知道的奇妙的東西，現在我是一個新的 DBA 和人。

  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';

我從您對Terry C 的回答的評論中看到，您正在尋找腳本分區函式和方案的 T-SQL 解決方案。雖然我認為 SMO 或 SSDT（通過 SSMS、Powershell、Visual Studio）是編寫 DDL 腳本的正確工具，但可以通過一些醜陋的 T-SQL 來完成。以下是此特定任務的範例腳本。

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';

引用自：https://dba.stackexchange.com/questions/171365