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

Comments

Plain text