pr.sql

use [arx]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Гавриляко К.Г.
-- Create date: 2018.03.01
-- Description:	Добавление следующего периода к сегментированой базе данных.
-- =============================================
-- exec arxCreateNextPeriod
ALTER PROCEDURE arxCreateNextPeriod
	@nextDate smalldatetime = null
AS
BEGIN
	SET NOCOUNT ON;

	-- получим следующий период
	if @nextDate is null begin
		set @nextDate = dateadd(month, 1, 
			convert(smalldatetime, 
				(select substring(max(name), 4, 6) + '01' from sys.filegroups where name like 'arx%'),
				112) 
			)
	end

	declare 
		@fileGroup nvarchar(10),
		@dataspace_id int,
		@sql nvarchar(max),
		@dir nvarchar(255)
		;

	set @dir = 'd:\db\arx';
	set @fileGroup = 'arx' + convert(varchar(6), @nextDate, 112);

	-- проверка сегмента
	select @dataspace_id = data_space_id from sys.filegroups where name = @fileGroup;
	if @dataspace_id is null begin
		set @sql = N'ALTER DATABASE [arx] ADD FILEGROUP ['+@fileGroup+N'];';
		print(@sql);
		exec (@sql);

		select @dataspace_id = data_space_id from sys.filegroups where name = @fileGroup;
	end;

	--select * from sys.filegroups, sys.database_files where sys.filegroups.data_space_id = sys.database_files.data_space_id
	--select * from sys.database_files

	if @dataspace_id is null
		print('error')

	-- Проверка наличия файла файловой группы
	if not exists (select * from sys.database_files where data_space_id = @dataspace_id/* or name = @fileGroup*/) begin
		-- Добавляем файл для выше созданной группы
		set @sql = N'ALTER DATABASE [arx] ADD FILE ' + 
				  N'(NAME = N''' + @fileGroup + N''',' +
				  N' FILENAME = N''' + @dir + N'\' + @fileGroup + '.ndf'',' + 
				  N' SIZE = 1MB,' +  
				  N' MAXSIZE = 100000MB,' +
				  N' FILEGROWTH = 5MB)' +
				  N' TO FILEGROUP ['+@fileGroup+N'];'
		print(@sql)
		exec (@sql)
	end;

	-- расширяем схему ренжирования
	set @sql = N'ALTER PARTITION SCHEME arxMonthRangeScheme NEXT USED '+@fileGroup;
	print(@sql)
	exec (@sql)

	-- добавляем новый сегмент в функцию ренжирования
	ALTER PARTITION FUNCTION [arxMonthRange]() SPLIT RANGE (cast(convert(varchar(8), @nextDate, 112) as smalldatetime));

	-- теперь надо изменить ограничения для поля ренжирования
	-- в нашем случае это дата
	select
		[ID]         = constr.object_id, 
		[TableName]  = tab.name,
		[ColName]    = col.name,
		[ConstrName] = constr.name,
		[Definition] = constr.definition
	into #constr
	from sys.tables tab
	join sys.columns col on tab.object_id = col.object_id
	join sys.check_constraints constr ON tab.object_id = constr.parent_object_id
	where tab.type = N'U' 
		and col.name = 'ArxDt'
		and constr.name like '%RangeMax%'

	--select * from #constr

	declare @id int;
	declare @tabname nvarchar(500), @colname nvarchar(500), @constrname nvarchar(500);
	declare @constrPeriod nvarchar(6);
	set @constrPeriod = convert(nvarchar(6), dateadd(month, 1, @nextDate), 112);
	while (select count(*) from #constr) > 0 begin
		select top 1 @id = [ID], @tabname = [TableName], @colname = [ColName], @constrname = [ConstrName] from #constr;

		-- Создадим новое ограничение для будущего периода
		-- ALTER TABLE tablename ADD CONSTRAINT tablename_RangeMax_201806 CHECK ([ArxDt] < '20180701')
		set @sql = N'ALTER TABLE '+@tabname+N' ADD CONSTRAINT '+@tabname+N'_RangeMax_'+@fileGroup+N' CHECK (['+@colname+N'] < '''+@constrPeriod+N''')'
		print(@sql)
		exec (@sql)
		
		-- Удалим старыйое ограничение
		set @sql = N'ALTER TABLE '+@tabname+N' DROP CONSTRAINT '+@constrname;
		print(@sql)
		exec (@sql)

		delete from #constr where ID = @id;
	end

	drop table #constr
END
GO