Procedura zwracająca nazwę FG na podstawie funkcji, schemy i kolumny partycjonującej

Na co dzień pracując z tabelami i indeksami partycjonującymi stworzyłem procedurę, która na podstawie parametrów wejściowych zwraca nazwę grupy plików, na której założona jest wybrana partycja.

Parametry wejściowe procedury to:

• @PartitionFunction – nazwa funkcji partycjonującej
• @PartitionSchema – nazwa schemy partycjonującej
• @RangeValue – wartość kolumny partycjonującej. Uwaga, obsługiwany jest typ INT . Dla innych typów kolumn partycjonujących należy zmodyfikować kod.

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO

IF OBJECT_ID(N'Tools.spGetFGfromPartitionScheme', 'P') IS NULL
EXEC('CREATE PROCEDURE spGetFGfromPartitionScheme AS SELECT 1')
GO

ALTER PROCEDURE [spGetFGfromPartitionScheme]
@DBName SYSNAME = 'FooBase'
,@PartitionFunction SYSNAME
,@PartitionSchema SYSNAME
,@RangeValue INT
,@FGPartitionName SYSNAME OUTPUT
AS
BEGIN
SET NOCOUNT ON

DECLARE @PartitionNumber int
DECLARE @sql nvarchar(max)

SET @sql = N'USE ['+@DBName+']
SELECT @PartNumber = $PARTITION.'+@PartitionFunction+'(@RangeVal)'

EXEC SP_EXECUTESQL @sql,N'@RangeVal int, @PartNumber INT OUTPUT',
@RangeVal = @RangeValue, @PartNumber = @PartitionNumber OUTPUT

SET @sql= N'

USE ['+ @DBName+']
SELECT @PartName = sf.name
FROM
sys.partition_schemes AS sps
INNER JOIN sys.partition_functions AS spf ON sps.function_id = spf.function_id
INNER JOIN sys.destination_data_spaces AS sdd ON sdd.partition_scheme_id = sps.data_space_id and sdd.destination_id <= spf.fanout
INNER JOIN sys.filegroups AS sf ON sf.data_space_id = sdd.data_space_id
WHERE
(sps.name=@PartSchema)
AND sdd.destination_id=@PartNumber
ORDER BY
sdd.destination_id ASC'

EXEC SP_EXECUTESQL @sql,N'@PartSchema nvarchar(4000), @PartNumber int, @PartName SYSNAME OUTPUT',
@PartSchema = @PartitionSchema, @PartNumber = @PartitionNumber, @PartName= @FGPartitionName OUTPUT
END

Poniżej przedstawiony jest sposób wywołania procedury.

DECLARE  @DBName	      SYSNAME = 'FooBase'	 
	,@PartitionFunction   SYSNAME = 'pfFooFunction'
	,@PartitionSchema     SYSNAME = 'psFooScheme'
	,@RangeValue	      INT     = 20170101
	,@PartitionSchemaName SYSNAME

EXEC spGetFGfromPartitionScheme	 @DBName= @DBName
			    	,@PartitionFunction = @PartitionFunction
				,@PartitionSchema   = @PartitionSchema
				,@RangeValue	    = @RangeValue
				,@FGPartitionName   = @PartitionSchemaName OUTPUT
SELECT @PartitionSchemaName