SQL Server 資料分割配置自動維護指令碼

標籤：

CREATE PROC Proc_partition_deal
AS
DECLARE @LatestValue DATETIME,
@LastValue DATETIME,
@EarlValue DATETIME,
@differ INT,
@New_Range_Value DATETIME,
@fileGroupName VARCHAR(200),
@sql VARCHAR(1000),
@P_Sch_Name VARCHAR(100),
@P_Fun_Name VARCHAR(100),
@P_Fun_Id INT

SET @fileGroupName=‘[PRIMARY]‘
SET @P_Sch_Name=‘Sche_Part_Date‘
SET @P_Fun_Name=‘Func_Part_Date‘
SET @sql=‘‘

IF NOT EXISTS( SELECT 1
FROM
Sys.partition_functions
WHERE name = @P_Fun_Name )
RETURN;

IF NOT EXISTS( SELECT 1
FROM
Sys.partition_schemes
WHERE name = @P_Sch_Name )
RETURN;

SELECT @P_Fun_Id = function_id
FROM
Sys.partition_functions
WHERE name = @P_Fun_Name

--合并分區函數
SELECT @EarlValue = CONVERT(VARCHAR(23), Min(value), 120)
FROM
Sys.PARTITION_RANGE_VALUES
WHERE function_id = @P_Fun_Id

SET @sql=‘ALTER Partition FUNCTION ‘ + @P_Fun_Name
+ ‘() MERGE Range (‘‘‘
+ CONVERT(VARCHAR(19), @EarlValue, 120)
+ ‘.000‘ + ‘‘‘)‘

EXEC(@sql)

--PRINT @sql
--增加資料分割配置
SET @sql=‘‘
SET @sql=‘ALTER PARTITION SCHEME ‘ + @P_Sch_Name
+ ‘ NEXT USED ‘ + @fileGroupName

EXEC(@sql)

--PRINT @sql
--增加分區函數
SELECT @LastValue = CONVERT(VARCHAR(23), Min(value), 120),
@LatestValue = CONVERT(VARCHAR(23), Max(value), 120)
FROM
( SELECT TOP 2 value
FROM
Sys.PARTITION_RANGE_VALUES
WHERE function_id = @P_Fun_Id
ORDER BY VALUE DESC ) PRV

SET @differ=Datediff(Dd, @LastValue, @LatestValue)
SET @New_Range_Value=Dateadd(Dd, @differ, CONVERT(DATE, @LatestValue))
SET @sql=‘‘
SET @sql=‘ALTER Partition FUNCTION ‘ + @P_Fun_Name
+ ‘() Split Range (‘‘‘
+ CONVERT(VARCHAR(19), @New_Range_Value, 120)
+ ‘.000‘ + ‘‘‘)‘

EXEC(@sql)
--PRINT @sql

SQL Server 資料分割配置自動維護指令碼