TJwgTKE0

1use [arx]
2GO
3
4SET ANSI_NULLS ON
5GO
6SET QUOTED_IDENTIFIER ON
7GO
8-- =============================================
9-- Author:		Гавриляко К.Г.
10-- Create date: 2018.03.01
11-- Description:	Добавление следующего периода к сегментированой базе данных.
12-- =============================================
13-- exec arxCreateNextPeriod
14ALTER PROCEDURE arxCreateNextPeriod
15	@nextDate smalldatetime = null
16AS
17BEGIN
18	SET NOCOUNT ON;
19
20	-- получим следующий период
21	if @nextDate is null begin
22		set @nextDate = dateadd(month, 1, 
23			convert(smalldatetime, 
24				(select substring(max(name), 4, 6) + '01' from sys.filegroups where name like 'arx%'),
25				112) 
26			)
27	end
28
29	declare 
30		@fileGroup nvarchar(10),
31		@dataspace_id int,
32		@sql nvarchar(max),
33		@dir nvarchar(255)
34		;
35
36	set @dir = 'd:\db\arx';
37	set @fileGroup = 'arx' + convert(varchar(6), @nextDate, 112);
38
39	-- проверка сегмента
40	select @dataspace_id = data_space_id from sys.filegroups where name = @fileGroup;
41	if @dataspace_id is null begin
42		set @sql = N'ALTER DATABASE [arx] ADD FILEGROUP ['+@fileGroup+N'];';
43		print(@sql);
44		exec (@sql);
45
46		select @dataspace_id = data_space_id from sys.filegroups where name = @fileGroup;
47	end;
48
49	--select * from sys.filegroups, sys.database_files where sys.filegroups.data_space_id = sys.database_files.data_space_id
50	--select * from sys.database_files
51
52	if @dataspace_id is null
53		print('error')
54
55	-- Проверка наличия файла файловой группы
56	if not exists (select * from sys.database_files where data_space_id = @dataspace_id/* or name = @fileGroup*/) begin
57		-- Добавляем файл для выше созданной группы
58		set @sql = N'ALTER DATABASE [arx] ADD FILE ' + 
59				  N'(NAME = N''' + @fileGroup + N''',' +
60				  N' FILENAME = N''' + @dir + N'\' + @fileGroup + '.ndf'',' + 
61				  N' SIZE = 1MB,' +  
62				  N' MAXSIZE = 100000MB,' +
63				  N' FILEGROWTH = 5MB)' +
64				  N' TO FILEGROUP ['+@fileGroup+N'];'
65		print(@sql)
66		exec (@sql)
67	end;
68
69	-- расширяем схему ренжирования
70	set @sql = N'ALTER PARTITION SCHEME arxMonthRangeScheme NEXT USED '+@fileGroup;
71	print(@sql)
72	exec (@sql)
73
74	-- добавляем новый сегмент в функцию ренжирования
75	ALTER PARTITION FUNCTION [arxMonthRange]() SPLIT RANGE (cast(convert(varchar(8), @nextDate, 112) as smalldatetime));
76
77	-- теперь надо изменить ограничения для поля ренжирования
78	-- в нашем случае это дата
79	select
80		[ID]         = constr.object_id, 
81		[TableName]  = tab.name,
82		[ColName]    = col.name,
83		[ConstrName] = constr.name,
84		[Definition] = constr.definition
85	into #constr
86	from sys.tables tab
87	join sys.columns col on tab.object_id = col.object_id
88	join sys.check_constraints constr ON tab.object_id = constr.parent_object_id
89	where tab.type = N'U' 
90		and col.name = 'ArxDt'
91		and constr.name like '%RangeMax%'
92
93	--select * from #constr
94
95	declare @id int;
96	declare @tabname nvarchar(500), @colname nvarchar(500), @constrname nvarchar(500);
97	declare @constrPeriod nvarchar(6);
98	set @constrPeriod = convert(nvarchar(6), dateadd(month, 1, @nextDate), 112);
99	while (select count(*) from #constr) > 0 begin
100		select top 1 @id = [ID], @tabname = [TableName], @colname = [ColName], @constrname = [ConstrName] from #constr;
101
102		-- Создадим новое ограничение для будущего периода
103		-- ALTER TABLE tablename ADD CONSTRAINT tablename_RangeMax_201806 CHECK ([ArxDt] < '20180701')
104		set @sql = N'ALTER TABLE '+@tabname+N' ADD CONSTRAINT '+@tabname+N'_RangeMax_'+@fileGroup+N' CHECK (['+@colname+N'] < '''+@constrPeriod+N''')'
105		print(@sql)
106		exec (@sql)
107		
108		-- Удалим старыйое ограничение
109		set @sql = N'ALTER TABLE '+@tabname+N' DROP CONSTRAINT '+@constrname;
110		print(@sql)
111		exec (@sql)
112
113		delete from #constr where ID = @id;
114	end
115
116	drop table #constr
117END
118GO