Reverse Engineering Partition Scheme Definition

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
thismight be of general interest, so I'm posting it here.

Here's the function's definition:

-- Definition of UDF fn_script_partition_schemeIF OBJECT_ID('dbo.fn_script_partition_scheme', 'FN') IS NOT NULL  DROP FUNCTION dbo.fn_script_partition_scheme;GOCREATE FUNCTION dbo.fn_script_partition_scheme(@ps AS SYSNAME)  RETURNS NVARCHAR(4000)ASBEGINRETURNN'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 schemeSTUFF(  (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');';ENDGO

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 purposesUSE master;GOIF DB_ID('testdb') IS NOT NULL  DROP DATABASE testdb;GOCREATE DATABASE testdb;GOUSE testdb;GO-- Create partition functionsCREATE 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 filegroupsALTER 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 schemesCREATE PARTITION SCHEME PS1 AS PARTITION PF1TO (FG1, FG2, FG3, FG4);CREATE PARTITION SCHEME PS2 AS PARTITION PF2TO (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