Recently someone posted a question in a private SQL trainers newsgroup
asking for a way to produce the code required to generate a partition
scheme. The request was to do this with code and not by using the scripting
capabilities of SSMS. I wrote a function called fn_script_partition_scheme
that accepts a partition scheme name as input and returns the
reverse-engineered CREATE PARTITION SCHEME code. I thought that
this might be of general interest, so I'm posting it here.

Here's the function's definition:

                              -- Definition of UDF fn_script_partition_scheme                              IF OBJECT_ID('dbo.fn_script_partition_scheme', 'FN') IS NOT NULL                                DROP FUNCTION dbo.fn_script_partition_scheme;                              GO                              CREATE FUNCTION dbo.fn_script_partition_scheme(@ps AS SYSNAME)                                RETURNS NVARCHAR(4000)                              AS                              BEGIN                              RETURN                              N'CREATE PARTITION SCHEME ' + QUOTENAME(@ps) + N' AS PARTITION '                              +                              -- Return partition function name                              -- based on input partition scheme name                              (SELECT QUOTENAME(PF.name)                               FROM sys.partition_schemes AS PS                                 JOIN sys.partition_functions AS PF                                   ON PS.function_id = PF.function_id                               where PS.name = @ps)                              +                               N'                              TO ('                              +                              -- Return concatenated list of filegroup names                              -- that participate in partition scheme                              STUFF(                                (SELECT N',' + QUOTENAME(DS.name) AS \[text()\]                                 FROM sys.partition_schemes AS PS                                   JOIN sys.destination_data_spaces AS DDS                                     ON PS.data_space_id = DDS.partition_scheme_id                                   JOIN sys.data_spaces AS DS                                     ON DDS.data_space_id = DS.data_space_id                                 WHERE PS.name = @ps                                 ORDER BY DDS.destination_id                                 FOR XML PATH('')), 1, 1, N'')                              +                              N');';                              END                              GO                              

To test the function, first run the following code that creates a database
called testdb, and within it two partition functions (PF1, PF2), seven
filegroups (FG1, FG2, FG3, FG4, FG5, FG6, FG7), and two partition
schemes (PS1, PS2):

                              -- Create database for test purposes                              USE master;                              GO                              IF DB_ID('testdb') IS NOT NULL                                DROP DATABASE testdb;                              GO                              CREATE DATABASE testdb;                              GO                              USE testdb;                              GO                              -- Create partition functions                              CREATE PARTITION FUNCTION PF1(INT) AS RANGE RIGHT FOR VALUES (1, 2, 3);                              CREATE PARTITION FUNCTION PF2(INT) AS RANGE RIGHT FOR VALUES (1, 2);                              -- Create filegroups                              ALTER DATABASE testdb ADD FILEGROUP FG7;                              ALTER DATABASE testdb ADD FILEGROUP FG6;                              ALTER DATABASE testdb ADD FILEGROUP FG5;                              ALTER DATABASE testdb ADD FILEGROUP FG4;                              ALTER DATABASE testdb ADD FILEGROUP FG3;                              ALTER DATABASE testdb ADD FILEGROUP FG2;                              ALTER DATABASE testdb ADD FILEGROUP FG1;                              -- Create partition schemes                              CREATE PARTITION SCHEME PS1 AS PARTITION PF1                              TO (FG1, FG2, FG3, FG4);                              CREATE PARTITION SCHEME PS2 AS PARTITION PF2                              TO (FG5, FG6, FG7);                              

Next, create the fn_script_partition_scheme function in the testdb database,
then run the following code to test it:

                              PRINT dbo.fn_script_partition_scheme(N'PS1');                              PRINT dbo.fn_script_partition_scheme(N'PS2');                              

And you should get the following output:

                              CREATE PARTITION SCHEME \[PS1\] AS PARTITION \[PF1\]                              TO (\[FG1\],\[FG2\],\[FG3\],\[FG4\]);                              CREATE PARTITION SCHEME \[PS2\] AS PARTITION \[PF2\]                              TO (\[FG5\],\[FG6\],\[FG7\]);                              

Cheers,
--
BG