首页 > 数据库技术 > 详细

SQL Server 分区方案自动维护脚本

时间:2016-07-19 18:27:24      阅读:157      评论:0      收藏:0      [点我收藏+]

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 分区方案自动维护脚本

原文:http://www.cnblogs.com/bocai0323/p/5685687.html

(0)
(0)
   
举报
评论 一句话评论(0
关于我们 - 联系我们 - 留言反馈 - 联系我们:wmxa8@hotmail.com
© 2014 bubuko.com 版权所有
打开技术之扣,分享程序人生!