SQL_scripts

1

2
--скрипт запускается на сервере приемнике, который должен быть смапен по базам с источником (в таблице #db_list)
3
--при @ops = 0 генерируется скрипт на устранение различий в схемах объектов, запускается отдельно, возможен выбор объектов
4
--при @ops = 1 выполняется накатывание всех найденных различий
5

6
set nocount, xact_abort on
7

8
declare
9
-- 0=только сгенерировать скрипт, 1=устранить все различия
10
	@ops int = 0
11
-- фильтр для таблиц (разделитель | )
12
	,@exclude_tables nvarchar(max) = ''
13
-- фильтр для индексов (разделитель | )
14
	,@exclude_indexes nvarchar(max) = ''
15
-- фильтр для модулей (разделитель | )
16
	,@exclude_modules nvarchar(max) = ''
17
-- фильтр для ограничений (разделитель | )
18
	,@exclude_defaults nvarchar(max) = ''
19
--минимальное значение даты принятое в системе	
20
	,@min_year nvarchar(4) = '1900'
21
--в какой базе хранить метаданные
22
	,@metadata_db sysname = 'TempDB'
23

24
--перечень серверов и их баз данных на которых возможен запуск скрипта
25
drop table if exists #db_list
26
select *
27
into #db_list
28
from (values
29
	('сервер источник 1',		'база данных 1',			'сервер приемник 1',		'файловая группа')
30
	,('сервер источник 1',		'база данных 2',			'сервер приемник 1',		'файловая группа')
31
	,('сервер источник 2',		'база данных 3',			'сервер приемник 2',		'файловая группа')
32
	,('сервер источник 3',		'база данных 4',			'сервер приемник 2',		'файловая группа')
33
) as t (src_srv, db, trg_srv, trg_filegr)
34

35
--*******************************************************************************************************--
36
declare
37
	@query nvarchar(max)
38
	,@applock_const nvarchar(50) = 'SYNCHRO'
39
	,@result int
40
	,@user sysname = system_user
41
	,@message nvarchar(max) = ''
42
	,@source_server sysname
43
	,@db sysname
44
	,@sysTable sysname
45
	,@target_filegroup sysname
46
	,@current_step nvarchar(10)
47
	,@step_desc nvarchar(max)
48
	,@sql_text nvarchar(max)
49
	,@sql_text2 nvarchar(max)
50
	,@synch_result_tbl sysname = @metadata_db + '..synch_result_' + format(getdate(), 'HHmmss')
51

52
if (@@servername like '%PROD%')
53
	throw 50000, 'СКРИПТ НЕ ДЛЯ ПРОДА !', 1
54

55
if cast(serverproperty('ProductMajorVersion') as int) < 14
56
	throw 50000, 'ВЕРСИЯ СЕРВЕРА МЛАДШЕ ДОПУСТИМОЙ !', 1
57

58
if exists (select * from #db_list where db = @metadata_db)
59
	throw 50000, 'ДЛЯ ХРАНЕНИЯ МЕТАДАННЫХ УКАЖИТЕ БД КОТОРАЯ НА УЧАСТВУЕТ В СИНХРОНИЗАЦИИ ДАННЫХ !', 1
60

61
if exists (select * from #db_list where db = 'tempdb')
62
	throw 50000, 'TEMPDB НЕ ДОЛЖНА УЧАВСТВОВАТЬ В СИНХРОНИЗАЦИИ ДАННЫХ !', 1
63

64
declare
65
	@c_nl char(2) = char(13)+char(10)
66
	,@t_source nvarchar(10) = 'source'
67
	,@t_target nvarchar(10) = 'target'
68
	,@t_schema nvarchar(10) = 'schema'
69
	,@t_table nvarchar(10) = 'table'
70
	,@t_column nvarchar(10) = 'column'
71
	,@t_index nvarchar(10) = 'index'
72
	,@t_constraint nvarchar(10) = 'constraint'
73
	,@t_function nvarchar(10) = 'function'
74
	,@t_procedure nvarchar(10) = 'procedure'
75
	,@t_trigger nvarchar(10) = 'trigger'
76
	,@t_view nvarchar(10) = 'view'
77
	,@t_synonym nvarchar(10) = 'synonym'
78
	,@t_sequence nvarchar(10) = 'sequence'
79
	,@t_partition_function nvarchar(20) = 'partition_function'
80
	,@t_partition_scheme nvarchar(20) = 'partition_scheme'
81
	,@t_create nvarchar(10) = 'create'
82
	,@t_drop nvarchar(10) = 'drop'
83
	,@t_alter nvarchar(10) = 'alter'
84
	,@marker nvarchar(10) = ';;;'
85

86
select @exclude_tables = string_agg(concat('t.[name] like ', quotename(trim([value]), '''')), ' or ')
87
from string_split(@exclude_tables, '|')
88
where [value] <> ''
89

90
select @exclude_indexes = string_agg(concat('i.[name] like ', quotename(trim([value]), '''')), ' or ')
91
from string_split(@exclude_indexes, '|')
92
where [value] <> ''
93

94
select @exclude_modules = string_agg(concat('o.[name] like ', quotename(trim([value]), '''')), ' or ')
95
from string_split(@exclude_modules, '|')
96
where [value] <> ''
97

98
select @exclude_defaults = string_agg(concat('df.[name] like ', quotename(trim([value]), '''')), ' or ')
99
from string_split(@exclude_defaults, '|')
100
where [value] <> ''
101

102
set @metadata_db = quotename(parsename(@metadata_db,1))
103

104
exec @result = sys.sp_getapplock
105
	@Resource = @applock_const
106
	,@LockMode = 'exclusive'
107
	,@LockOwner = 'session'
108
	,@LockTimeout = 0
109
	,@DbPrincipal = @user
110

111
if @result < 0
112
begin
113
    select @message = string_agg(request_session_id, ',')
114
	from sys.dm_tran_locks (nolock)
115
	where
116
		resource_type = 'APPLICATION'
117
		and request_owner_type = 'SESSION'
118
		and resource_description like '%' + @applock_const + '%'
119

120
	set @message = concat('ЗАКРОЙТЕ СЕССИИ: ', @message, ' !')
121
	
122
	;throw 50000, @message, 1
123
end
124

125
drop table if exists [#SCRIPT_TABLE]
126

127
create table [#SCRIPT_TABLE] (
128
	db sysname not null
129
	,current_step int not null
130
	,step_desc nvarchar(max) not null
131
	,sql_text nvarchar(max) not null
132
	,object_desc nvarchar(128) not null
133
	,index ix_db clustered (db)
134
)
135

136
declare cursDB cursor for
137
	select
138
		src_srv
139
		,db
140
		,trg_filegr
141
	from #db_list
142
	where @@servername like '%' + trg_srv + '%'
143
open cursDB
144
fetch next from cursDB into @source_server, @db, @target_filegroup
145
while @@fetch_status = 0
146
begin
147

148
	begin--получение метаданных
149

150
	drop table if exists [#SYS_schemas]
151
	drop table if exists [#SYS_tables]
152
	drop table if exists [#SYS_columns]
153
	drop table if exists [#SYS_columns_alter]
154
	drop table if exists [#SYS_indexes]
155
	drop table if exists [#SYS_constraints]
156
	drop table if exists [#SYS_modules]
157
	drop table if exists [#SYS_synonyms]
158
	drop table if exists [#SYS_sequences]
159
	drop table if exists [#SYS_partition_schemes]
160
	drop table if exists [#SYS_partition_functions]
161
	drop table if exists [#SYS_partition_alter]
162

163
	create table [#SYS_schemas] (
164
		[purpose] nvarchar(10) not null
165
		,[server] nvarchar(128) not null
166
		,[db] nvarchar(128) not null
167
		,[id] int not null
168
		,[name] nvarchar(128) not null
169
		,[purpose.id] as concat(purpose, ':', [id]) persisted not null
170
	)
171
	
172
	create table [#SYS_tables] (
173
		[purpose] nvarchar(10) not null
174
		,[server] nvarchar(128) not null
175
		,[db] nvarchar(128) not null
176
		,[id] int not null
177
		,[schema] nvarchar(128) not null
178
		,[name] nvarchar(128) not null
179
		,[is filetable] bit
180
		,[is in-memory] bit
181
		,[durability] nvarchar(128)
182
		,[is external] bit
183
		,[is node] bit
184
		,[is edge] bit
185
		,[is encrypted] bit
186
		,[is fake] bit
187
		,[is temporal] bit
188
		,[lock escalation] nvarchar(128)
189
		,[schema.table] as ([schema] + '.' + [name]) persisted not null
190
		,[purpose.id] as concat(purpose, ':', [id]) persisted not null
191
		,[not supported] as ([is filetable] | [is in-memory] | [is external] | [is node] | [is edge] | [is encrypted] | [is fake] | [is temporal])
192
	)
193
	
194
	create table [#SYS_columns] (
195
		[purpose] nvarchar(10) not null
196
		,[server] nvarchar(128) not null
197
		,[db] nvarchar(128) not null
198
		,[table id] int not null
199
		,[name] nvarchar(128) not null
200
		,[ordinal position] int
201
		,[is nullable] bit
202
		,[is rowguidcol] bit
203
		,[is identity] bit
204
		,[is filestream] bit
205
		,[is xml_document] bit
206
		,[is sparse] bit
207
		,[is column_set] bit
208
		,[is hidden] bit
209
		,[is masked] bit
210
		,[seed value] bigint
211
		,[increment value] bigint
212
		,[last value] bigint
213
		,[is computed] bit
214
		,[definition for computed] nvarchar(max)
215
		,[is persisted] bit
216
		,[collation] nvarchar(128)
217
		,[type name] nvarchar(128)
218
		,[type size] nvarchar(128)
219
		,[default for type] nvarchar(128)
220
		,[purpose.table id] as concat(purpose, ':', [table id]) persisted not null
221
	)
222
	
223
	create table [#SYS_columns_alter] (
224
		[schema.table] nvarchar(128) not null
225
		,[name] nvarchar(128) not null
226
		,primary key ([schema.table], [name])
227
	)
228
	
229
	create table [#SYS_indexes] (
230
		[purpose] nvarchar(10) not null
231
		,[server] nvarchar(128) not null
232
		,[db] nvarchar(128) not null
233
		,[table id] int not null
234
		,[name] nvarchar(128)
235
		,[key column names] nvarchar(max)
236
		,[included column names] nvarchar(max)
237
		,[index type] nvarchar(128)
238
		,[ignore dup key] bit
239
		,[is unique] bit
240
		,[is padded] bit
241
		,[is disabled] bit
242
		,[is hypothetical] bit
243
		,[fill factor] int
244
		,[allow row locks] bit
245
		,[allow page locks] bit
246
		,[optimize for sequential key] bit
247
		,[has filter] bit
248
		,[filter definition] nvarchar(max)
249
		,[is primary key] bit
250
		,[is unique constraint] bit
251
		,[rowcount] bigint
252
		,[data_size] bigint
253
		,[data space id] int not null
254
		,[partition scheme] nvarchar(128)
255
		,[partition column] nvarchar(128)
256
		,[purpose.table id] as concat(purpose, ':', [table id]) persisted not null
257
	)
258
	
259
	create table [#SYS_constraints] (
260
		[purpose] nvarchar(10) not null
261
		,[server] nvarchar(128) not null
262
		,[db] nvarchar(128) not null
263
		,[table id] int not null
264
		,[name] nvarchar(128) not null
265
		,[column names] nvarchar(max)
266
		,[type] nvarchar(128)
267
		,[definition] nvarchar(max)
268
		,[definition_full] nvarchar(max)
269
		,[purpose.table id] as concat(purpose, ':', [table id]) persisted not null
270
	)
271
	
272
	create table [#SYS_modules] (
273
		[purpose] nvarchar(10) not null
274
		,[server] nvarchar(128) not null
275
		,[db] nvarchar(128) not null
276
		,[id] int not null
277
		,[schema] nvarchar(128) not null
278
		,[name] nvarchar(128) not null
279
		,[object type] nvarchar(2) not null
280
		,[is trg disabled] bit
281
		,[is encrypted] bit
282
		,[is binding] bit
283
		,[definition] nvarchar(max)
284
		,[type desc] nvarchar(128)
285
		,[parent id] int
286
		,[parent schema] nvarchar(128)
287
		,[parent name] nvarchar(128)
288
		,[schema.object] as ([schema] + '.' + [name]) persisted not null
289
		,[parent schema.object] as ([parent schema] + '.' + [parent name])
290
		,[purpose.id] as concat(purpose, ':', [id]) persisted not null
291
	)
292
	
293
	create table [#SYS_synonyms] (
294
		[purpose] nvarchar(10) not null
295
		,[server] nvarchar(128) not null
296
		,[db] nvarchar(128) not null
297
		,[id] int not null
298
		,[schema] nvarchar(128) not null
299
		,[name] nvarchar(128) not null
300
		,[base object name] nvarchar(2000)
301
		,[schema.object] as ([schema] + '.' + [name]) persisted not null
302
		,[purpose.id] as concat(purpose, ':', [id]) persisted not null
303
	)
304
	
305
	create table [#SYS_sequences] (
306
		[purpose] nvarchar(10) not null
307
		,[server] nvarchar(128) not null
308
		,[db] nvarchar(128) not null
309
		,[id] int not null
310
		,[schema] nvarchar(128) not null
311
		,[name] nvarchar(128) not null
312
		,[type] nvarchar(128)
313
		,[start value] bigint
314
		,[increment] bigint
315
		,[minimum value] bigint
316
		,[maximum value] bigint
317
		,[is cycling] bit
318
		,[is cached] bit
319
		,[cache size] int
320
		,[last used value] bigint
321
		,[schema.object] as ([schema] + '.' + [name]) persisted not null
322
		,[purpose.id] as concat(purpose, ':', [id]) persisted not null
323
	)
324
	
325
	create table [#SYS_partition_schemes] (
326
		[purpose] nvarchar(10) not null
327
		,[server] nvarchar(128) not null
328
		,[db] nvarchar(128) not null
329
		,[name] nvarchar(128) not null
330
		,[function id] int not null
331
		,[data space id] int not null
332
		,[definition] nvarchar(max) not null
333
		,[definition checksum] as checksum(upper(replace(replace(replace(replace([definition],char(32),''),char(9),''),char(10),''),char(13),''))) persisted not null
334
		,[purpose.name] as concat(purpose, ':', [name]) persisted not null
335
	)
336

337
	create table [#SYS_partition_functions] (
338
		[purpose] nvarchar(10) not null
339
		,[server] nvarchar(128) not null
340
		,[db] nvarchar(128) not null
341
		,[id] int not null
342
		,[name] nvarchar(128) not null
343
		,[data space id] int not null
344
		,[definition] nvarchar(max) not null
345
		,[definition checksum] as checksum(upper(replace(replace(replace(replace([definition],char(32),''),char(9),''),char(10),''),char(13),''))) persisted not null
346
		,[purpose.name] as concat(purpose, ':', [name]) persisted not null
347
	)
348

349
	create table [#SYS_partition_alter] (
350
		[type] nvarchar(128) not null
351
		,[name] nvarchar(128) not null
352
		,primary key ([type], [name])
353
	)
354

355
	declare cursMD cursor for
356
		select [name]
357
		from (values
358
			('schemas')
359
			,('tables')
360
			,('columns')
361
			,('objects')
362
			,('types')
363
			,('identity_columns')
364
			,('computed_columns')
365
			,('indexes')
366
			,('index_columns')
367
			,('default_constraints')
368
			,('check_constraints')
369
			,('foreign_keys')
370
			,('foreign_key_columns')
371
			,('synonyms')
372
			,('sequences')
373
			,('sql_modules')
374
			,('triggers')
375
			,('partitions')
376
			,('partition_functions')
377
			,('partition_schemes')
378
			,('partition_parameters')
379
			,('partition_range_values')
380
			,('data_spaces')
381
			,('destination_data_spaces')
382
			,('filegroups')
383
			,('allocation_units')
384
		) as sysTables([name])
385

386
	open cursMD
387
	fetch next from cursMD into @sysTable
388
	while @@fetch_status = 0
389
	begin
390
		set @query = '
391
			drop table if exists ' + @metadata_db + '..SRC_' + @sysTable + '
392
			select * 
393
			into ' + @metadata_db + '..SRC_' + @sysTable + '
394
			from openrowset(
395
				''SQLNCLI''
396
				,N''Server=' + @source_server + ';Database=' + @db + ';Trusted_Connection=yes;''
397
				,''select * from ' + quotename(@db) + '.sys.' + @sysTable + ' with(nolock)'')
398
		'
399
		exec(@query)
400
--print concat('--Получена ', @sysTable, ' (', @@rowcount, ')')
401
		fetch next from cursMD into @sysTable
402
	end
403
	close cursMD
404
	deallocate cursMD
405
	
406
	--схемы
407
	set @query = '
408
		use ' + quotename(@db) + '
409
		set tran isolation level read uncommitted
410
		select
411
			@purpose
412
			,@server
413
			,db_name()
414
			,s.schema_id as [object id]
415
			,quotename(s.name)
416
		from sys.schemas as s
417
		where
418
			s.schema_id > 4 
419
			and s.schema_id < 1000
420
	'
421
	insert into [#SYS_schemas] ([purpose],[server],[db],[id],[name])
422
	exec sp_executesql
423
			@query
424
			,N'@server nvarchar(50), @purpose nvarchar(10)'
425
			,@server = @@servername,@purpose = @t_target
426
	
427
	set @query = replace(@query, 'sys.', @metadata_db + '..SRC_')
428
	
429
	insert into [#SYS_schemas] ([purpose],[server],[db],[id],[name])
430
	exec sp_executesql
431
			@query
432
			,N'@server nvarchar(50), @purpose nvarchar(10)'
433
			,@server = @source_server,@purpose = @t_source
434
	
435
	--схемы секционирования
436
	set @query = '
437
		use ' + quotename(@db) + '
438
		set tran isolation level read uncommitted
439
		select
440
			@purpose
441
			,@server
442
			,db_name()
443
			,ps.name
444
			,pf.function_id
445
			,ps.data_space_id
446
			,''create partition scheme '' + quotename(ps.[name]) + '' as partition '' + quotename(pf.[name]) + '' to ('' + stuff(fg.[name].value(''.'', ''nvarchar(max)''), 1, 1, '''') + '')'' as [definition]
447
		from
448
			sys.partition_schemes as ps
449
			inner join sys.partition_functions as pf
450
				on pf.function_id = ps.function_id
451
			cross apply (
452
				select '','' + quotename(/*fg.name*/''default'')
453
				from
454
					sys.data_spaces ds
455
					inner join sys.destination_data_spaces as dds
456
						on dds.partition_scheme_id = ds.data_space_id
457
					inner join sys.filegroups as fg
458
						on fg.data_space_id = dds.data_space_id
459
				where ps.data_space_id = ds.data_space_id
460
				order by dds.destination_id
461
				for xml path(''''), type
462
			) as fg([name])
463
	'
464
	
465
	insert into [#SYS_partition_schemes] ([purpose],[server],[db],[name],[function id],[data space id],[definition])
466
	exec sp_executesql
467
			@query
468
			,N'@server nvarchar(50), @purpose nvarchar(10)'
469
			,@server = @@servername,@purpose = @t_target
470
	
471
	set @query = replace(@query, 'sys.', @metadata_db + '..SRC_')
472
	
473
	insert into [#SYS_partition_schemes] ([purpose],[server],[db],[name],[function id],[data space id],[definition])
474
	exec sp_executesql
475
			@query
476
			,N'@server nvarchar(50), @purpose nvarchar(10)'
477
			,@server = @source_server,@purpose = @t_source
478

479
	--функции секционирования
480
	set @query = '
481
		use ' + quotename(@db) + '
482
		set tran isolation level read uncommitted
483
		select
484
			@purpose
485
			,@server
486
			,db_name()
487
			,pf.function_id
488
			,pf.[name]
489
			,ps.data_space_id
490
			,''create partition function '' + quotename(pf.[name]) + '' ('' + t.[name]
491
			+ isnull(''('' + case
492
					when t.[name] in (''varchar'', ''char'', ''varbinary'', ''binary'', ''nvarchar'', ''nchar'')
493
						then cast(iif(t.name like ''n%'', pp.max_length / 2, pp.max_length) as nvarchar(10))
494
					when t.[name] in (''datetime2'', ''time2'', ''datetimeoffset'') 
495
						then cast(pp.scale as nvarchar(10))
496
					when t.[name] in (''decimal'', ''numeric'')
497
						then cast(pp.[precision] as nvarchar(10)) + '','' + cast(pp.scale as nvarchar(10))
498
					else null end + '')'', '''')
499
			+ '') '' + ''as range '' + iif(pf.boundary_value_on_right = 1, ''right'', ''left'') + '' for values (''
500
		    + stuff(rg.[value].value(''.'', ''nvarchar(max)''), 1, 1, '''')
501
		    + '')'' as [definition]
502
		from
503
			sys.partition_functions as pf
504
			inner join sys.partition_schemes as ps
505
				on pf.function_id = ps.function_id
506
			inner join sys.partition_parameters as pp
507
				on pp.function_id = pf.function_id
508
			inner join sys.types as t
509
				on t.user_type_id = pp.user_type_id
510
			cross apply (
511
				select
512
		            '',''
513
		            + case
514
		                  when sql_variant_property(r.[value],  ''basetype'') in (''char'', ''varchar'', ''nchar'', ''nvarchar'', ''uniqueidentifier'') 
515
		                    then quotename(cast(r.[value] as nvarchar(4000)), '''''''')
516
		                  when sql_variant_property(r.[value], ''basetype'') in (''date'', ''datetime'', ''smalldatetime'', ''datetime2'', ''datetimeoffset'') 
517
		                    then quotename(format(cast(r.[value] as datetime2), ''yyyy-MM-ddTHH:mm:ss.fffffff K''), '''''''')
518
		                  when sql_variant_property(r.[value], ''basetype'') = ''time'' 
519
		                    then quotename(format(cast(r.[value] as time), ''hh\:mm\:ss\.fffffff''),'''''''')
520
		                  when sql_variant_property(r.[value], ''basetype'') in (''binary'', ''varbinary'') 
521
		                    then convert(nvarchar(4000), r.[value], 1)
522
		                  else cast(r.[value] as nvarchar(4000))
523
		              end
524
				from sys.partition_range_values as r
525
				where pf.[function_id] = r.[function_id]
526
				order by r.boundary_id
527
				for xml path(''''), type
528
		    ) as rg([value])		
529
	'
530
	
531
	insert into [#SYS_partition_functions] ([purpose],[server],[db],[id],[name],[data space id],[definition])
532
	exec sp_executesql
533
			@query
534
			,N'@server nvarchar(50), @purpose nvarchar(10)'
535
			,@server = @@servername,@purpose = @t_target
536
	
537
	set @query = replace(@query, 'sys.', @metadata_db + '..SRC_')
538
	
539
	insert into [#SYS_partition_functions] ([purpose],[server],[db],[id],[name],[data space id],[definition])
540
	exec sp_executesql
541
			@query
542
			,N'@server nvarchar(50), @purpose nvarchar(10)'
543
			,@server = @source_server,@purpose = @t_source
544

545
	--таблицы
546
	set @query = '
547
		use ' + quotename(@db) + '
548
		set tran isolation level read uncommitted
549
		select
550
			@purpose
551
			,@server
552
			,db_name()
553
			,t.object_id as [table id]
554
			,quotename(s.name)
555
			,quotename(t.name)
556
			,t.is_filetable
557
			,t.is_memory_optimized
558
			,concat(t.durability,'' '',quotename(t.durability_desc))
559
			,t.is_external
560
			,t.is_node
561
			,t.is_edge
562
			,0 --objectproperty(t.object_id,''isencrypted'')
563
			,0 --objectproperty(t.object_id,''tableisfake'')
564
			,0 --objectproperty(t.object_id,''tabletemporaltype'')
565
			,t.lock_escalation_desc
566
		from
567
			sys.tables as t
568
			inner join sys.schemas as s
569
				on s.schema_id = t.schema_id
570
		' + iif(@exclude_tables <> '', 'where not (' + @exclude_tables + ')', '')
571
	
572
	insert into [#SYS_tables] ([purpose],[server],[db],[id],[schema],[name],[is filetable],[is in-memory],[durability],[is external],[is node],[is edge],[is encrypted],[is fake],[is temporal],[lock escalation])
573
	exec sp_executesql
574
			@query
575
			,N'@server nvarchar(50), @purpose nvarchar(10)'
576
			,@server = @@servername,@purpose = @t_target
577
	
578
	set @query = replace(@query, 'sys.', @metadata_db + '..SRC_')
579
	
580
	insert into [#SYS_tables] ([purpose],[server],[db],[id],[schema],[name],[is filetable],[is in-memory],[durability],[is external],[is node],[is edge],[is encrypted],[is fake],[is temporal],[lock escalation])
581
	exec sp_executesql
582
			@query
583
			,N'@server nvarchar(50), @purpose nvarchar(10)'
584
			,@server = @source_server,@purpose = @t_source
585
	
586
	--колонки
587
	set @query = '
588
		use ' + quotename(@db) + '
589
		set tran isolation level read uncommitted
590
		select
591
			@purpose
592
			,@server
593
			,db_name()
594
			,c.object_id as [table id]
595
			,quotename(c.name)
596
			,row_number() over(partition by c.object_id order by c.column_id) as [ordinal position] --,columnproperty(c.object_id, c.name, ''ordinal'') as [ordinal position]
597
			,c.is_nullable
598
			,c.is_rowguidcol
599
			,c.is_identity
600
			,c.is_filestream
601
			,c.is_xml_document
602
			,c.is_sparse
603
			,c.is_column_set
604
			,c.is_hidden
605
			,c.is_masked
606
			,cast(ic.seed_value as bigint)
607
			,cast(ic.increment_value as bigint)
608
			,cast(ic.last_value as bigint)
609
			,c.is_computed
610
			,isnull(cc.[definition],'''')
611
			,cc.is_persisted
612
			,c.collation_name
613
			,t.name
614
			,isnull(''('' + case
615
				when c.is_computed = 1
616
					then null
617
				when t.[name] in (''varchar'', ''char'', ''varbinary'', ''binary'', ''nvarchar'', ''nchar'')
618
					then iif(c.max_length = -1, ''max'', cast(iif(t.name like ''n%'', c.max_length / 2, c.max_length)  as nvarchar(10))) 
619
				when t.[name] in (''datetime2'', ''time2'', ''datetimeoffset'') 
620
					then cast(c.scale as nvarchar(10))
621
				when t.[name] in (''decimal'', ''numeric'')
622
					then cast(c.[precision] as nvarchar(10)) + '','' + cast(c.scale as nvarchar(10))
623
				else null end + '')'','''')
624
			,''('' + case
625
				when t.[name] in (''image'',''varbinary'',''binary''/*,timestamp*/) then ''0x0''
626
				when t.[name] in (''uniqueidentifier'') then ''''''00000000-0000-0000-0000-000000000000''''''
627
				when t.[name] in (''text'',''ntext'',''varchar'',''char'',''nvarchar'',''nchar'',''xml'') then ''''''''''''
628
				when t.[name] in (''tinyint'',''smallint'',''int'',''real'',''money'',''float'',''bit'',''decimal'',''numeric'',''smallmoney'',''bigint'') then ''0''
629
				when t.[name] in (''time'',''date'',''datetime2'',''datetimeoffset'',''smalldatetime'',''datetime'') then ''''''' + @min_year + '-01-01''''''
630
					else '''''''''''' end + '')''
631
		from
632
			sys.columns as c
633
			inner join sys.objects as o
634
				on c.object_id = o.object_id
635
				and o.is_ms_shipped = 0
636
			inner join sys.types as t
637
				on c.user_type_id = t.user_type_id
638
			left join sys.identity_columns as ic
639
				on c.[object_id] = ic.[object_id]
640
				and c.column_id = ic.column_id
641
			left join sys.computed_columns as cc
642
				on c.[object_id] = cc.[object_id]
643
				and c.column_id = cc.column_id
644
	'
645
	
646
	insert into [#SYS_columns] ([purpose],[server],[db],[table id],[name],[ordinal position],[is nullable],[is rowguidcol],[is identity],[is filestream],[is xml_document],[is sparse],[is column_set],[is hidden],[is masked],[seed value],[increment value],[last value],[is computed],[definition for computed],[is persisted],collation,[type name],[type size],[default for type])
647
	exec sp_executesql
648
			@query
649
			,N'@server nvarchar(50), @purpose nvarchar(10)'
650
			,@server = @@servername,@purpose = @t_target
651
	
652
	set @query = replace(@query, 'sys.', @metadata_db + '..SRC_')
653
	
654
	insert into [#SYS_columns] ([purpose],[server],[db],[table id],[name],[ordinal position],[is nullable],[is rowguidcol],[is identity],[is filestream],[is xml_document],[is sparse],[is column_set],[is hidden],[is masked],[seed value],[increment value],[last value],[is computed],[definition for computed],[is persisted],collation,[type name],[type size],[default for type])
655
	exec sp_executesql
656
			@query
657
			,N'@server nvarchar(50), @purpose nvarchar(10)'
658
			,@server = @source_server, @purpose = @t_source
659
	
660
	--индексы
661
	set @query = '
662
		use ' + quotename(@db) + '
663
		set tran isolation level read uncommitted
664
		select
665
			@purpose
666
			,@server
667
			,db_name()
668
			,i.object_id
669
			,quotename(i.name)
670
			,stuff(ic.[key].value(''/x[1]'',''nvarchar(max)''),1,1,'''')
671
			,isnull(stuff(ic2.[incl].value(''/x[1]'',''nvarchar(max)''),1,1,''''),'''')
672
			,lower(i.type_desc)
673
			,i.ignore_dup_key
674
			,i.is_unique
675
			,i.is_padded
676
			,i.is_disabled
677
			,i.is_hypothetical
678
			,iif(i.fill_factor = 0, 100, i.fill_factor)
679
			,i.allow_row_locks
680
			,i.allow_page_locks
681
			,i.optimize_for_sequential_key
682
			,i.has_filter
683
			,isnull(i.filter_definition,'''')
684
			,i.is_primary_key
685
			,i.is_unique_constraint
686
			,au.[rowcount]
687
			,au.[data_size]
688
			,i.data_space_id
689
			,ps.[partition scheme]
690
			,ps.[partition column]
691
		from
692
			sys.indexes as i
693
			inner join sys.objects as o
694
				on i.object_id = o.object_id
695
				and o.is_ms_shipped = 0
696
			cross apply (
697
				select
698
					 '','' + quotename(c.name) + iif(ic.is_descending_key = 0, '' asc'', '' desc'')
699
				from
700
					sys.index_columns as ic
701
					inner join sys.columns as c
702
						on c.object_id = ic.object_id
703
						and ic.column_id = c.column_id
704
				where
705
					ic.object_id = i.object_id
706
					and ic.index_id = i.index_id
707
					and ic.is_included_column = 0
708
				order by 
709
					ic.key_ordinal
710
				for xml path(''''),root(''x''),type
711
			) as ic([key])
712
			outer apply (
713
				select
714
					'','' + quotename(c.name)
715
				from
716
					sys.index_columns as ic
717
					inner join sys.columns as c
718
						on c.object_id = ic.object_id
719
						and ic.column_id = c.column_id
720
				where
721
					ic.object_id = i.object_id
722
					and ic.index_id = i.index_id
723
					and ic.is_included_column = 1
724
				order by 
725
					ic.key_ordinal
726
				for xml path(''''),root(''x''),type
727
			) as ic2(incl)
728
			outer apply (
729
				select
730
					ps.[name] as [partition scheme]
731
					,c.[name] as [partition column]
732
				from
733
					sys.tables as t
734
					inner join sys.index_columns as ic   
735
						on ic.[object_id] = i.[object_id]   
736
						and ic.index_id = i.index_id   
737
						and ic.partition_ordinal >= 1
738
					inner join sys.partition_schemes as ps   
739
						on ps.data_space_id = i.data_space_id   
740
					inner join sys.columns as c   
741
						on t.[object_id] = c.[object_id]   
742
						and ic.column_id = c.column_id   
743
				where t.[object_id] = i.[object_id]   
744
			) as ps
745
			left join (
746
				select
747
					p.object_id
748
					,p.index_id
749
					,sum(p.[rows]) as [rowcount]
750
					,sum(a.data_pages) * 8 as [data_size]
751
				from
752
					sys.partitions as p
753
					inner join sys.allocation_units as a
754
						on p.partition_id = a.container_id
755
				group by
756
					p.object_id
757
					,p.index_id
758
			) as au
759
				on au.object_id = i.object_id
760
				and au.index_id = i.index_id
761
		' + iif(@exclude_indexes <> '', 'where not (' + @exclude_indexes + ')', '')
762

763
	insert into [#SYS_indexes] ([purpose],[server],[db],[table id],[name],[key column names],[included column names],[index type],[ignore dup key],[is unique],[is padded],[is disabled],[is hypothetical],[fill factor],[allow row locks],[allow page locks],[optimize for sequential key],[has filter],[filter definition],[is primary key],[is unique constraint],[rowcount],[data_size],[data space id],[partition scheme],[partition column])
764
	exec sp_executesql
765
			@query
766
			,N'@server nvarchar(50), @purpose nvarchar(10)'
767
			,@server = @@servername,@purpose = @t_target
768
	
769
	set @query = replace(@query, 'sys.', @metadata_db + '..SRC_')
770
	
771
	insert into [#SYS_indexes] ([purpose],[server],[db],[table id],[name],[key column names],[included column names],[index type],[ignore dup key],[is unique],[is padded],[is disabled],[is hypothetical],[fill factor],[allow row locks],[allow page locks],[optimize for sequential key],[has filter],[filter definition],[is primary key],[is unique constraint],[rowcount],[data_size],[data space id],[partition scheme],[partition column])
772
	exec sp_executesql
773
			@query
774
			,N'@server nvarchar(50), @purpose nvarchar(10)'
775
			,@server = @source_server, @purpose = @t_source
776
		
777
	--ограничения
778
	set @query = '
779
		use ' + quotename(@db) + '
780
		set tran isolation level read uncommitted
781
		select
782
			@purpose
783
			,@server
784
			,db_name()
785
			,df.parent_object_id
786
			,quotename(df.[name])
787
			,quotename(c.name)
788
			,df.[type]
789
			,df.[definition]
790
			,''default '' + df.[definition] + '' for '' + quotename(c.name)
791
		from
792
			sys.default_constraints as df
793
			inner join sys.objects as o
794
				on o.object_id = df.parent_object_id
795
			inner join sys.columns as c
796
				on c.object_id = df.parent_object_id
797
				and c.column_id = df.parent_column_id
798
		where
799
			o.is_ms_shipped <> 1
800
			' + iif(@exclude_defaults <> '', 'and not (' + @exclude_defaults + ')', '') + '
801
		
802
		select
803
			@purpose
804
			,@server
805
			,db_name()
806
			,ch.parent_object_id
807
			,quotename(ch.[name])
808
			,''''
809
			,ch.[type]
810
			,ch.[definition]
811
			,''check '' + ch.[definition]
812
		from sys.check_constraints as ch
813
			
814
		select
815
			@purpose
816
			,@server
817
			,db_name()
818
			,fk.parent_object_id
819
			,quotename(fk.[name])
820
			,stuff(parent_fkc.[parent colunms].value(''/x[1]'',''nvarchar(max)''),1,1,'''')
821
			,fk.[type]
822
			,''''
823
			,'' foreign key ('' + stuff(parent_fkc.[parent colunms].value(''/x[1]'',''nvarchar(max)''),1,1,'''') + '') references '' + quotename(s.name) + ''.'' + quotename(t.name) collate Cyrillic_General_100_CI_AS
824
				+ '' ('' + stuff(fkc.[columns].value(''/x[1]'',''nvarchar(max)''),1,1,'''') + '') on update '' + lower(replace(fk.update_referential_action_desc,''_'','' '')) + '' on delete '' + lower(replace(fk.delete_referential_action_desc,''_'','' ''))
825
		from
826
			sys.foreign_keys as fk
827
			inner join sys.tables as t
828
				on t.object_id = fk.referenced_object_id
829
			inner join sys.schemas as s
830
				on s.schema_id = t.schema_id
831
			cross apply (
832
				select
833
					'','' + quotename(c.name)
834
				from
835
					sys.foreign_key_columns as fkc
836
					inner join sys.columns as c
837
						on c.object_id = fk.parent_object_id
838
						and c.column_id = fkc.parent_column_id
839
				where fkc.constraint_object_id = fk.object_id
840
				order by fkc.constraint_column_id
841
				for xml path(''''),root(''x''),type
842
			) parent_fkc([parent colunms])
843
			cross apply (
844
				select
845
					'','' + quotename(c.name)
846
				from
847
					sys.foreign_key_columns as fkc
848
					inner join sys.columns as c
849
						on c.object_id = fk.referenced_object_id
850
						and c.column_id = fkc.referenced_column_id
851
				where fkc.constraint_object_id = fk.object_id
852
				order by fkc.constraint_column_id
853
				for xml path(''''),root(''x''),type
854
			) fkc([columns])
855
	'
856
	
857
	insert into [#SYS_constraints] ([purpose],[server],[db],[table id],[name],[column names],[type],[definition],[definition_full])
858
	exec sp_executesql
859
			@query
860
			,N'@server nvarchar(50), @purpose nvarchar(10)'
861
			,@server = @@servername,@purpose = @t_target
862
	
863
	set @query = replace(@query, 'sys.', @metadata_db + '..SRC_')
864
	
865
	insert into [#SYS_constraints] ([purpose],[server],[db],[table id],[name],[column names],[type],[definition],[definition_full])
866
	exec sp_executesql
867
			@query
868
			,N'@server nvarchar(50), @purpose nvarchar(10)'
869
			,@server = @source_server, @purpose = @t_source
870
	
871
	--модули
872
	set @query = '
873
		use ' + quotename(@db) + '
874
		set tran isolation level read uncommitted
875
		select
876
			@purpose
877
			,@server
878
			,db_name()
879
			,o.object_id
880
			,quotename(s.name)
881
			,quotename(o.name)
882
			,o.type
883
			,isnull(t.is_disabled, 0) --objectproperty(o.object_id,''ExecIsTriggerDisabled'')
884
			,0 --objectproperty(o.object_id,''IsEncrypted'')
885
			,isnull(sm.is_schema_bound, 0) --objectproperty(o.object_id,''IsSchemaBound'')
886
			,sm.[definition] --object_definition(o.object_id)
887
			,lower(iif(charindex(''_'',o.type_desc) = 0, o.type_desc, right(o.type_desc, charindex(''_'',reverse(o.type_desc))-1)))
888
			,o.parent_object_id
889
			,quotename(s2.name)
890
			,quotename(o2.name)
891
		from
892
			sys.objects as o
893
			inner join sys.schemas as s
894
				on s.schema_id = o.schema_id
895
			left join sys.triggers as t
896
				on o.object_id = t.object_id
897
			left join sys.sql_modules as sm
898
				on sm.object_id = o.object_id
899
			left join sys.objects as o2
900
				on o.parent_object_id = o2.object_id
901
			left join sys.schemas as s2
902
				on s2.schema_id = o2.schema_id
903
		where
904
			o.type in (''FN'',''IF'',''P'',''V'',''TF'',''TR'')
905
			and o.is_ms_shipped = 0
906
		' + iif(@exclude_modules <> '', 'and not (' + @exclude_modules + ')', '')
907
			
908
	insert into [#SYS_modules] ([purpose],[server],[db],[id],[schema],[name],[object type],[is trg disabled],[is encrypted],[is binding],[definition],[type desc],[parent id],[parent schema],[parent name])
909
	exec sp_executesql
910
			@query
911
			,N'@server nvarchar(50), @purpose nvarchar(10)'
912
			,@server = @@servername,@purpose = @t_target
913
	
914
	set @query = replace(@query, 'sys.', @metadata_db + '..SRC_')
915
	
916
	insert into [#SYS_modules] ([purpose],[server],[db],[id],[schema],[name],[object type],[is trg disabled],[is encrypted],[is binding],[definition],[type desc],[parent id],[parent schema],[parent name])
917
	exec sp_executesql
918
			@query
919
			,N'@server nvarchar(50), @purpose nvarchar(10)'
920
			,@server = @source_server, @purpose = @t_source
921
	
922
	--синонимы
923
	set @query = '
924
		use ' + quotename(@db) + '
925
		set tran isolation level read uncommitted
926
		select
927
			@purpose
928
			,@server
929
			,db_name()
930
			,s.object_id
931
			,quotename(s2.name)
932
			,quotename(s.name)
933
			,s.base_object_name
934
		from
935
			sys.synonyms as s
936
			inner join sys.schemas as s2
937
				on s.schema_id = s2.schema_id
938
		'
939
		
940
	insert into [#SYS_synonyms] ([purpose],[server],[db],[id],[schema],[name],[base object name])
941
	exec sp_executesql
942
			@query
943
			,N'@server nvarchar(50), @purpose nvarchar(10)'
944
			,@server = @@servername,@purpose = @t_target
945
	
946
	set @query = replace(@query, 'sys.', @metadata_db + '..SRC_')
947
	
948
	
949
	insert into [#SYS_synonyms] ([purpose],[server],[db],[id],[schema],[name],[base object name])
950
	exec sp_executesql
951
			@query
952
			,N'@server nvarchar(50), @purpose nvarchar(10)'
953
			,@server = @source_server, @purpose = @t_source
954
	
955
	--последовательности
956
	set @query = '
957
		use ' + quotename(@db) + '
958
		set tran isolation level read uncommitted
959
		select
960
			@purpose
961
			,@server
962
			,db_name()
963
			,s.object_id
964
			,quotename(s2.name)
965
			,quotename(s.name)
966
			,iif(t.[name] in (''decimal'',''numeric''), concat(t.[name],''('',s.precision,'')''),t.[name])
967
			,cast(s.start_value as bigint)
968
			,cast(s.increment as bigint)
969
			,cast(s.minimum_value as bigint)
970
			,cast(s.maximum_value as bigint)
971
			,s.is_cycling
972
			,s.is_cached
973
			,s.cache_size
974
			,cast(s.last_used_value as bigint)
975
		from
976
			sys.sequences as s
977
			inner join sys.types as t
978
				on t.user_type_id = s.user_type_id
979
			inner join sys.schemas as s2
980
				on s.schema_id = s2.schema_id
981
	'
982
	
983
	insert into [#SYS_sequences] ([purpose],[server],[db],[id],[schema],[name],[type],[start value],increment,[minimum value],[maximum value],[is cycling],[is cached],[cache size],[last used value])
984
	exec sp_executesql
985
			@query
986
			,N'@server nvarchar(50), @purpose nvarchar(10)'
987
			,@server = @@servername,@purpose = @t_target
988
	
989
	set @query = replace(@query, 'sys.', @metadata_db + '..SRC_')
990
	
991
	insert into [#SYS_sequences] ([purpose],[server],[db],[id],[schema],[name],[type],[start value],increment,[minimum value],[maximum value],[is cycling],[is cached],[cache size],[last used value])
992
	exec sp_executesql
993
			@query
994
			,N'@server nvarchar(50), @purpose nvarchar(10)'
995
			,@server = @source_server, @purpose = @t_source
996
	
997
	end
998

999
	begin--сводная таблица
1000

1001
	drop table if exists [#PIVOT_TABLE]
1002

1003
	create table [#PIVOT_TABLE] (
1004
		[object desc] nvarchar(128)
1005
		,[parent object desc] nvarchar(128)
1006
		,[source server] nvarchar(128)
1007
		,[source db] nvarchar(128)
1008
		,[source parent object] nvarchar(128)
1009
		,[source object] nvarchar(128)
1010
		,[target server] nvarchar(128)
1011
		,[target db] nvarchar(128)
1012
		,[target parent object] nvarchar(128)
1013
		,[target object] nvarchar(128)
1014
		,[sql text] nvarchar(max)
1015
		,[action] nvarchar(10) index ix_act nonclustered
1016
		,[order] int
1017
		,[info] nvarchar(128)
1018
	)
1019

1020
	--схемы
1021
	insert into [#PIVOT_TABLE] ([object desc],[parent object desc],[source server],[source db],[source parent object],[source object],[target server],[target db],[target parent object],[target object],[sql text])
1022
	select
1023
		@t_schema
1024
		,null
1025
		,s.[server]
1026
		,s.db
1027
		,null
1028
		,s.[name]
1029
		,t.[server]
1030
		,t.db
1031
		,null
1032
		,t.[name]
1033
		,case
1034
			when s.[name] is null
1035
				then 'drop schema ' + t.[name]
1036
			when t.[name] is null
1037
				then 'create schema ' + s.[name]
1038
			else '' end
1039
	from
1040
		[#SYS_schemas] as s
1041
		full join [#SYS_schemas] as t
1042
			on s.purpose <> t.purpose
1043
			and s.[name] = t.[name]
1044
	where
1045
		(s.purpose = @t_source
1046
			or t.purpose = @t_target)
1047
	option (maxdop 8)
1048
		
1049
	--функции секционирования
1050
	insert into [#PIVOT_TABLE] ([object desc],[parent object desc],[source server],[source db],[source parent object],[source object],[target server],[target db],[target parent object],[target object],[sql text])
1051
	select
1052
		@t_partition_function
1053
		,null
1054
		,s.[server]
1055
		,s.db
1056
		,''
1057
		,s.[name]
1058
		,t.[server]
1059
		,t.db
1060
		,''
1061
		,t.[name]
1062
		,case
1063
			when s.[name] is null
1064
				then 'drop partition function ' + quotename(t.[name])
1065
			when t.[name] is null
1066
				then s.[definition]
1067
			else '' end
1068
	from
1069
		[#SYS_partition_functions] as s
1070
		full join [#SYS_partition_functions] as t
1071
			on s.purpose <> t.purpose
1072
			and t.[definition checksum] = s.[definition checksum]
1073
	where
1074
		(s.purpose = @t_source
1075
			or t.purpose = @t_target)
1076
	option (maxdop 8)
1077

1078
	insert into [#SYS_partition_alter] ([type], [name])
1079
	select distinct
1080
		@t_partition_function
1081
		,isnull(pt.[source object],pt.[target object])
1082
	from [#PIVOT_TABLE] as pt
1083
	where
1084
		pt.[object desc] = @t_partition_function
1085
		and pt.[sql text] <> ''
1086
	option (maxdop 8)
1087

1088
	--схемы секционирования
1089
	;with [partition_schemes] as (
1090
		select
1091
			ps.*
1092
			,iif(pa.[name] is not null, 1, 0) as [re-create]
1093
		from
1094
			[#SYS_partition_schemes] as ps
1095
			inner join [#SYS_partition_functions] as pf
1096
				on ps.[function id] = pf.id
1097
				and pf.purpose = ps.purpose
1098
			left join [#SYS_partition_alter] as pa
1099
				on pa.[name] = pf.[name]
1100
				and pa.[type] = @t_partition_function
1101
	)
1102
	insert into [#PIVOT_TABLE] ([object desc],[parent object desc],[source server],[source db],[source parent object],[source object],[target server],[target db],[target parent object],[target object],[sql text])
1103
	select
1104
		@t_partition_scheme
1105
		,null
1106
		,s.[server]
1107
		,s.db
1108
		,''
1109
		,s.[name]
1110
		,t.[server]
1111
		,t.db
1112
		,''
1113
		,t.[name]
1114
		,case
1115
			when s.[name] is null
1116
				then 'drop partition scheme ' + quotename(t.[name])
1117
			when t.[name] is null
1118
				then s.[definition]
1119
			else '' end
1120
	from
1121
		[partition_schemes] as s
1122
		full join [partition_schemes] as t
1123
			on s.purpose <> t.purpose
1124
			and t.[definition checksum] = s.[definition checksum]
1125
			and (t.[re-create] | s.[re-create] = 0)
1126
	where
1127
		(s.purpose = @t_source
1128
			or t.purpose = @t_target)
1129
	option (maxdop 8)
1130

1131
	insert into [#SYS_partition_alter] ([type], [name])
1132
	select distinct
1133
		@t_partition_scheme
1134
		,isnull(pt.[source object],pt.[target object])
1135
	from [#PIVOT_TABLE] as pt
1136
	where
1137
		pt.[object desc] = @t_partition_scheme
1138
		and pt.[sql text] <> ''
1139
	option (maxdop 8)
1140

1141
	--таблицы
1142
	insert into [#PIVOT_TABLE] ([object desc],[parent object desc],[source server],[source db],[source parent object],[source object],[target server],[target db],[target parent object],[target object],[sql text])
1143
	select
1144
		@t_table
1145
		,null
1146
		,s.[server]
1147
		,s.db
1148
		,null
1149
		,s.[schema.table]
1150
		,t.[server]
1151
		,t.db
1152
		,null
1153
		,t.[schema.table]
1154
		,case
1155
			when t.[schema.table] is null
1156
				then 'create table ' + s.[schema.table] + ' ( ' + stuff((
1157
					select
1158
						',' + c.[name] + ' ' + case
1159
							when c.[is computed] = 1
1160
								then ' as ' + c.[definition for computed] + iif(c.[is persisted] = 1,' persisted','')
1161
							when c.[is identity] = 1
1162
								then c.[type name] + c.[type size] + concat(' identity (',c.[seed value],',',c.[increment value],')')
1163
							else
1164
								c.[type name] + c.[type size] + ' ' + isnull(' collate ' + c.collation,'') + iif(c.[is nullable] = 0,' not','') + ' null'
1165
							end + @c_nl
1166
					from [#SYS_columns] as c
1167
					where
1168
						c.[purpose.table id] = s.[purpose.id]
1169
					order by c.[ordinal position]
1170
					for xml path(''),root('x'),type
1171
				).value('/x[1]','nvarchar(max)'),1,1,'') + ' )'
1172
			when s.[schema.table] is null
1173
				then 'drop table ' + t.[schema.table]
1174
			when s.[lock escalation] <> t.[lock escalation]
1175
				then 'alter table ' + t.[schema.table] + ' set (lock_escalation = ' + s.[lock escalation] + ')'
1176
			else '' end
1177
	from
1178
		( [#SYS_tables] as s
1179
		full join [#SYS_tables] as t
1180
			on t.purpose <> s.purpose
1181
			and t.[schema.table] = s.[schema.table] )
1182
	where
1183
		(s.purpose = @t_source
1184
			or t.purpose = @t_target)
1185
	option (maxdop 8)
1186
	
1187
	--колонки
1188
	;with [columns] as (
1189
		select
1190
			c.*
1191
			,t.[schema.table]
1192
			,concat(c.[type name],c.[type size],c.[is nullable]) as [type check]
1193
			,checksum(upper(replace(replace(replace(replace(c.[definition for computed],char(32),''),char(9),''),char(10),''),char(13),''))) as [computed check]
1194
		from
1195
			[#SYS_tables] as t
1196
			inner join [#SYS_tables] as t2
1197
				on t.purpose <> t2.purpose
1198
				and t2.[schema.table] = t.[schema.table]
1199
			inner join [#SYS_columns] as c
1200
				on c.[purpose.table id] = t.[purpose.id]
1201
	)
1202
	insert into [#PIVOT_TABLE] ([object desc],[parent object desc],[source server],[source db],[source parent object],[source object],[target server],[target db],[target parent object],[target object],[sql text], [order])
1203
	select
1204
		@t_column
1205
		,@t_table
1206
		,s.[server]
1207
		,s.db
1208
		,s.[schema.table]
1209
		,s.[name]
1210
		,t.[server]
1211
		,t.db
1212
		,t.[schema.table]
1213
		,t.[name]
1214
		,case
1215
			when s.[name] is null
1216
				then 'alter table ' + t.[schema.table] + ' drop column ' + t.[name]
1217
			when t.[name] is null
1218
				then 'alter table ' + s.[schema.table] + ' add ' + s.[name] + ' ' + case
1219
							when s.[is computed] = 1
1220
								then ' as ' + s.[definition for computed] + iif(s.[is persisted] = 1,' persisted','')
1221
							when s.[is identity] = 1
1222
								then s.[type name] + s.[type size] + concat(' identity (',s.[seed value],',',s.[increment value],')')
1223
							else
1224
								s.[type name] + s.[type size] + isnull(' collate ' + s.collation,'') + case
1225
									when s.[is nullable] = 1
1226
										then ' null'
1227
									when s.[is nullable] = 0 and s.[name] <> '[timestamp]'
1228
										then
1229
											' null ' + @c_nl
1230
											+ 'alter table ' + s.[schema.table] + ' disable trigger all' + @c_nl
1231
											+ 'exec ('' update ' + replace(s.[schema.table] + ' set ' + s.[name] + ' = ' + isnull(df.[definition],s.[default for type]),'''','''''') + ''')' + @c_nl
1232
											+ 'alter table ' + s.[schema.table] + ' alter column ' + s.[name] + ' ' + s.[type name] + s.[type size] + ' not null' +@c_nl
1233
											+ 'alter table ' + s.[schema.table] + ' enable trigger all'
1234
									else iif(s.[is nullable] = 0,' not','') + ' null' end end
1235
			when (s.[is identity] = 0 and t.[is identity] = 1) or (s.[is computed] = 0 and t.[is computed] = 1)
1236
				then
1237
					'alter table ' + t.[schema.table] + ' add [$temp_i_clmn] ' + s.[type name] + s.[type size] + isnull(' collate ' + s.collation,'') + ' null' + @c_nl
1238
					+ 'alter table ' + s.[schema.table] + ' disable trigger all' + @c_nl
1239
					+ 'exec ('' update ' + t.[schema.table] + ' set [$temp_i_clmn] = ' + t.[name] + ''')' + @c_nl
1240
					+ 'alter table ' + t.[schema.table] + ' drop column ' + t.[name] + @c_nl
1241
					+ 'exec sp_rename ''' + t.[schema.table] + '.[$temp_i_clmn]'',''' + parsename(s.[name],1) + ''', ''column''' + @c_nl
1242
					+ iif(s.[is nullable] = 0, 'alter table ' + t.[schema.table] + ' alter column ' + t.[name] + ' not null ' + @c_nl,'')
1243
					+ 'alter table ' + s.[schema.table] + ' enable trigger all'
1244
			when t.[name] <> '[timestamp]' and s.[type check] <> t.[type check]
1245
				then
1246
					iif(s.[is nullable] = 0 and t.[is nullable] = 1,'update ' + t.[schema.table] + ' set ' + t.[name] + ' = ' + isnull(df.[definition],s.[default for type]) + ' where ' + t.[name] + ' is null' + @c_nl,'')
1247
					+ 'alter table ' + t.[schema.table] + ' alter column ' + t.[name] + ' ' + s.[type name] + s.[type size] + ' ' + isnull(' collate ' + s.collation,'') + iif(s.[is nullable] = 0,' not','') + ' null'
1248
			else '' end
1249
		,isnull(s.[ordinal position], t.[ordinal position])
1250
	from
1251
		( [columns] as s
1252
		full join [columns] as t
1253
			on t.purpose <> s.purpose
1254
			and t.[schema.table] = s.[schema.table]
1255
			and t.[name] = s.[name]
1256
			and not (s.[is computed] = 1 and t.[is computed] = 0)
1257
			and not (s.[is identity] = 1 and t.[is identity] = 0)
1258
			and isnull(t.[increment value],0) = isnull(s.[increment value],0)
1259
			and t.[computed check] = s.[computed check] )
1260
		outer apply (
1261
			select c.[definition]
1262
			from [#SYS_constraints] as c
1263
			where
1264
				c.[purpose.table id] = s.[purpose.table id]
1265
				and c.purpose = @t_source
1266
				and c.[column names] = s.[name]
1267
				and c.[type] = 'D') as df
1268
	where
1269
		(s.purpose = @t_source
1270
			or t.purpose = @t_target)
1271
	
1272
	insert into [#SYS_columns_alter] ([schema.table], [name])
1273
	select distinct
1274
		isnull(pt.[source parent object],pt.[target parent object])
1275
		,isnull(pt.[source object],pt.[target object])
1276
	from [#PIVOT_TABLE] as pt
1277
	where
1278
		pt.[object desc] = @t_column
1279
		and pt.[sql text] <> ''
1280
	option (maxdop 8)
1281
	
1282
	--индексы
1283
	;with [indexes] as (
1284
		select
1285
			i.*
1286
			,t.[schema.table]
1287
			,upper(concat(i.[key column names], i.[included column names])) as [column check]
1288
			,concat(i.[allow row locks],i.[allow page locks],i.[optimize for sequential key],i.[ignore dup key]) as [options check]
1289
			,checksum(upper(replace(replace(replace(replace(i.[filter definition],char(32),''),char(9),''),char(10),''),char(13),''))) as [filter check]
1290
			,iif(ca.[count] > 0, 1, 0) | iif(pa.[name] is not null, 1, 0) as [re-create]
1291
		from
1292
			[#SYS_indexes] as i
1293
			inner join (
1294
				select
1295
					[schema.table]
1296
					,[purpose.id]
1297
				from [#SYS_tables] as t
1298
				union all
1299
				select
1300
					[schema.object]
1301
					,[purpose.id]
1302
				from [#SYS_modules] as t
1303
				where [object type] = 'V' 
1304
			) as t
1305
				on i.[purpose.table id] = t.[purpose.id]
1306
			left join [#SYS_partition_alter] as pa
1307
				on i.[partition scheme] = pa.[name]
1308
				and pa.[type] = @t_partition_scheme
1309
			outer apply (
1310
				select count(*) as [count]
1311
				from [#SYS_columns_alter] as ca
1312
				where
1313
					ca.[schema.table] = t.[schema.table]
1314
					and (charindex(ca.[name],i.[key column names]) > 0 or charindex(ca.[name],i.[included column names]) > 0)
1315
				) as ca
1316
		where i.[index type] <> 'heap'
1317
	)
1318
	insert into [#PIVOT_TABLE] ([object desc],[parent object desc],[source server],[source db],[source parent object],[source object],[target server],[target db],[target parent object],[target object],[sql text],[info])
1319
	select
1320
		@t_index
1321
		,@t_table
1322
		,s.[server]
1323
		,s.db
1324
		,s.[schema.table]
1325
		,s.[name]
1326
		,t.[server]
1327
		,t.db
1328
		,t.[schema.table]
1329
		,t.[name]
1330
		,case
1331
			when t.[name] is null
1332
				then
1333
					iif(s.[is primary key] | s.[is unique constraint] = 1,
1334
						'alter table ' + s.[schema.table] + ' add constraint ' + s.[name] + iif(s.[is primary key] = 1, ' primary key ', ' unique ') + s.[index type] + ' ( ' + s.[key column names] + ' ) ',
1335
						'create ' + iif(s.[is unique] = 1,' unique ', '') + s.[index type] + ' index ' + s.[name] + ' on ' + s.[schema.table] + ' ( ' + s.[key column names] + ' ) '
1336
					+ iif(s.[included column names] = '', '', concat(' include (',s.[included column names],') '))
1337
					+ iif(s.[has filter] = 1,' where ' + s.[filter definition],''))
1338
					+ ' with (
1339
						fillfactor = ' + cast(s.[fill factor] as nvarchar(3))	+ '
1340
						,allow_page_locks = ' + iif(s.[allow page locks] = 1, 'on', 'off') + '
1341
						,allow_row_locks = ' + iif(s.[allow row locks] = 1, 'on', 'off') + '
1342
						,ignore_dup_key = ' + iif(s.[ignore dup key] = 1, 'on', 'off') + '
1343
						,optimize_for_sequential_key = ' + iif(s.[optimize for sequential key] = 1, 'on', 'off') + ' ) '
1344
					+ ' on ' + iif(s.[partition scheme] is null, quotename(@target_filegroup), quotename(s.[partition scheme]) + '('+ quotename(s.[partition column]) + ')')
1345
			when s.[schema.table] is null
1346
				then
1347
					iif(t.[is primary key] | t.[is unique constraint] = 1,
1348
						'alter table ' + t.[schema.table] + ' drop constraint ' + t.[name] + @c_nl,
1349
						'drop index ' + t.[name] + ' on ' + t.[schema.table])
1350
			when t.[options check] <> s.[options check]
1351
				then 'alter index ' + t.[name] + ' on ' + t.[schema.table] + ' set (
1352
						optimize_for_sequential_key = ' + iif(s.[optimize for sequential key] = 1, 'on', 'off') +
1353
						iif(s.[is primary key] | s.[is unique constraint] = 1, '', ',ignore_dup_key = ' + iif(s.[ignore dup key] = 1, 'on', 'off')) + '
1354
						,allow_page_locks = ' + iif(s.[allow page locks] = 1, 'on', 'off') + '
1355
						,allow_row_locks = ' + iif(s.[allow row locks] = 1, 'on', 'off') + ' )'
1356
			else '' end
1357
			+ iif(s.[is disabled] = 1 and t.[is disabled] = 0, @c_nl + 'alter index ' + s.[name] + ' on ' + s.[schema.table] + ' disable', '')
1358
		,concat(isnull(s.[rowcount], t.[rowcount]), ' rows ≥', isnull(s.[data_size], t.[data_size]), ' KB')
1359
	from
1360
		[indexes] as s
1361
		full join [indexes] as t
1362
			on t.purpose <> s.purpose
1363
			and t.[schema.table] = s.[schema.table]
1364
			--and t.[name] = s.[name]
1365
			and t.[column check] = s.[column check]
1366
			and t.[is unique] = s.[is unique]
1367
			and t.[index type] = s.[index type]
1368
			--and t.[has filter] = s.[has filter]
1369
			and t.[filter check] = s.[filter check]
1370
			and (t.[re-create] | s.[re-create] = 0)
1371
	where
1372
		(s.purpose = @t_source
1373
			or t.purpose = @t_target)
1374
	option (maxdop 8)
1375
	
1376
	--ограничения
1377
	;with [constraints] as (
1378
		select
1379
			t.[schema.table]
1380
			,c.*
1381
			,iif(ca.[count] > 0, 1, 0) as [re-create]
1382
		from
1383
			[#SYS_tables] as t
1384
			inner join [#SYS_constraints] as c
1385
				on c.[purpose.table id] = t.[purpose.id]
1386
			outer apply (
1387
				select count(*) as [count]
1388
				from [#SYS_columns_alter] as ca
1389
				where
1390
					ca.[schema.table] = t.[schema.table]
1391
					and charindex(ca.[name],c.[column names]) > 0
1392
				) as ca
1393
	)
1394
	insert into [#PIVOT_TABLE] ([object desc],[parent object desc],[source server],[source db],[source parent object],[source object],[target server],[target db],[target parent object],[target object],[sql text])
1395
	select
1396
		@t_constraint
1397
		,@t_table
1398
		,s.[server]
1399
		,s.db
1400
		,s.[schema.table]
1401
		,s.[name]
1402
		,t.[server]
1403
		,t.db
1404
		,t.[schema.table]
1405
		,t.[name]
1406
		,case
1407
			when s.[name] is null
1408
				then 'alter table ' + t.[schema.table] + ' drop constraint ' + t.[name]
1409
			when t.[name] is null
1410
				then 'alter table ' + s.[schema.table] + ' with nocheck add ' + s.[definition_full]
1411
			else '' end
1412
	from
1413
		[constraints] as s
1414
		full join [constraints] as t
1415
			on t.purpose <> s.purpose
1416
			and t.[schema.table] = s.[schema.table]
1417
			and t.[definition_full] = s.[definition_full]
1418
			and (t.[re-create] | s.[re-create] = 0)
1419
	where
1420
		(s.purpose = @t_source
1421
			or t.purpose = @t_target)
1422
	option (maxdop 8)
1423
	
1424
	--модули
1425
	;with [modules] as (
1426
		select
1427
			m.*
1428
			,checksum(upper(replace(replace(replace(replace(m.[definition],char(32),''),char(9),''),char(10),''),char(13),''))) as [object check]
1429
		from [#SYS_modules] as m
1430
		where m.[is encrypted] = 0
1431
	)
1432
	insert into [#PIVOT_TABLE] ([object desc],[parent object desc],[source server],[source db],[source parent object],[source object],[target server],[target db],[target parent object],[target object],[sql text])
1433
	select
1434
		isnull(s.[type desc], t.[type desc])
1435
		,null
1436
		,s.[server]
1437
		,s.db
1438
		,null
1439
		,s.[schema.object]
1440
		,t.[server]
1441
		,t.db
1442
		,null
1443
		,t.[schema.object]
1444
		,case
1445
			when s.[schema.object] is not null and t.[schema.object] is null
1446
				then s.[definition]
1447
			when s.[schema.object] is null and t.[schema.object] is not null
1448
				then 'drop ' + t.[type desc] + t.[schema.object]
1449
			else '' end
1450
			+ iif(s.[object type] = 'TR' and (t.[schema.object] is null or s.[is trg disabled] <> t.[is trg disabled])
1451
				,iif(s.[is trg disabled] = 1
1452
					,@marker + 'disable trigger ' + s.[schema.object] + ' on ' + s.[parent schema.object] + @marker
1453
					,@marker + 'enable trigger ' + s.[schema.object] + ' on ' + s.[parent schema.object] + @marker)
1454
				,'')
1455
	from
1456
		[modules] as s
1457
		full join [modules] as t
1458
			on t.purpose <> s.purpose
1459
			and t.[object check] = s.[object check]
1460
	where
1461
		(s.purpose = @t_source
1462
			or t.purpose = @t_target)
1463
	option (maxdop 8)
1464
	
1465
	--синонимы
1466
	;with [synonyms] as (
1467
		select s.*
1468
		from [#SYS_synonyms] as s
1469
	)
1470
	insert into [#PIVOT_TABLE] ([object desc],[parent object desc],[source server],[source db],[source parent object],[source object],[target server],[target db],[target parent object],[target object],[sql text])
1471
	select
1472
		@t_synonym
1473
		,null
1474
		,s.[server]
1475
		,s.db
1476
		,null
1477
		,s.[schema.object]
1478
		,t.[server]
1479
		,t.db
1480
		,null
1481
		,t.[schema.object]
1482
		,case
1483
			when s.[schema.object] is not null and t.[schema.object] is null
1484
				then 'create synonym ' + s.[schema.object] + ' for ' + s.[base object name]
1485
			when s.[schema.object] is null and t.[schema.object] is not null
1486
				then 'drop synonym ' + t.[schema.object]
1487
			else '' end
1488
	from
1489
		[synonyms] as s
1490
		full join [synonyms] as t
1491
			on t.purpose <> s.purpose
1492
			and t.[schema.object] = s.[schema.object]
1493
			and t.[base object name] = s.[base object name]
1494
	where
1495
		(s.purpose = @t_source
1496
			or t.purpose = @t_target)
1497
	option (maxdop 8)
1498
	
1499
	--последовательности
1500
	;with [sequences] as (
1501
		select
1502
			s.*
1503
			,concat(s.[start value],s.increment,s.[minimum value],s.[maximum value],s.[is cycling],s.[is cached],s.[cache size]) as [options check]
1504
		from [#SYS_sequences] as s
1505
	)
1506
	insert into [#PIVOT_TABLE] ([object desc],[parent object desc],[source server],[source db],[source parent object],[source object],[target server],[target db],[target parent object],[target object],[sql text])
1507
	select
1508
		@t_sequence
1509
		,null
1510
		,s.[server]
1511
		,s.db
1512
		,null
1513
		,s.[schema.object]
1514
		,t.[server]
1515
		,t.db
1516
		,null
1517
		,t.[schema.object]
1518
		,case
1519
			when s.[schema.object] is not null and t.[schema.object] is null
1520
				then concat('create sequence ',s.[schema.object],' as ',s.[type],' start with ',s.[start value],' increment by ',s.increment,' minvalue ',s.[minimum value],' maxvalue '
1521
												,s.[maximum value],iif(s.[is cycling] = 1,'','no'),' cycle ',iif(s.[cache size] is null,' no cache',concat(' cache ',s.[cache size])))
1522
			when s.[schema.object] is null and t.[schema.object] is not null
1523
				then 'drop sequence ' + t.[schema.object]
1524
			else '' end
1525
	from
1526
		[sequences] as s
1527
		full join [sequences] as t
1528
			on t.purpose <> s.purpose
1529
			and t.[schema.object] = s.[schema.object]
1530
			and s.[options check] = t.[options check]
1531
	where
1532
		(s.purpose = @t_source
1533
			or t.purpose = @t_target)
1534
	
1535
	update [#PIVOT_TABLE] set
1536
		[action] = case
1537
			when [source object] is not null and [target object] is null and [sql text] <> ''
1538
				then @t_create
1539
			when [source object] is null and [target object] is not null and [sql text] <> ''
1540
				then @t_drop
1541
			when [source object] is not null and [target object] is not null and [sql text] <> ''
1542
				then @t_alter
1543
			else '' end
1544
	end
1545

1546
	insert into [#SCRIPT_TABLE] (db, current_step, step_desc, sql_text, object_desc)
1547
	select
1548
		@db
1549
		,row_number() over(order by
1550
			case
1551
				when pt.[object desc] in (@t_function, @t_procedure, @t_trigger) and pt.[action] = @t_drop then 1
1552
				when pt.[object desc] = @t_constraint and pt.[action] = @t_drop then 2
1553
				when pt.[object desc] = @t_index and pt.[action] = @t_drop then 3
1554
				when pt.[object desc] = @t_view and pt.[action] = @t_drop then 4
1555
				when pt.[object desc] = @t_column and pt.[action] = @t_drop then 5
1556
				when pt.[object desc] = @t_table and pt.[action] = @t_drop then 6
1557
				when pt.[object desc] = @t_partition_scheme and pt.[action] = @t_drop then 7
1558
				when pt.[object desc] = @t_partition_function and pt.[action] = @t_drop then 8
1559
				when pt.[object desc] = @t_schema and pt.[action] = @t_create then 9
1560
				when pt.[object desc] = @t_partition_function and pt.[action] = @t_create then 10
1561
				when pt.[object desc] = @t_partition_scheme and pt.[action] = @t_create then 11
1562
				when pt.[object desc] in (@t_column, @t_table, @t_constraint) then 12
1563
				when pt.[object desc] = @t_view and pt.[action] = @t_alter then 13
1564
				when pt.[object desc] = @t_view and pt.[action] = @t_create then 14
1565
				when pt.[object desc] = @t_index and pt.[action] = @t_alter then 15
1566
				when pt.[object desc] = @t_index and pt.[action] = @t_create then 16
1567
				when pt.[object desc] in (@t_function, @t_procedure, @t_trigger) and pt.[action] = @t_alter then 17
1568
				when pt.[object desc] in (@t_function, @t_procedure, @t_trigger) and pt.[action] = @t_create then 18
1569
				when pt.[object desc] = @t_synonym and pt.[action] = @t_drop then 19
1570
				when pt.[object desc] = @t_synonym and pt.[action] = @t_create then 20
1571
				when pt.[object desc] = @t_sequence and pt.[action] = @t_drop then 21
1572
				when pt.[object desc] = @t_sequence and pt.[action] = @t_create then 22
1573
				when pt.[object desc] = @t_schema and pt.[action] = @t_drop then 23
1574
				else 100 end
1575
			,isnull(isnull(pt.[source parent object],pt.[source object]),isnull(pt.[target parent object],pt.[target object]))
1576
			,case
1577
				when pt.[object desc] = @t_column and pt.[action] = @t_alter then 1
1578
				when pt.[object desc] = @t_column and pt.[action] = @t_create then 2
1579
				when pt.[object desc] = @t_table and pt.[action] = @t_alter then 3
1580
				when pt.[object desc] = @t_table and pt.[action] = @t_create then 4
1581
				when pt.[object desc] = @t_constraint and pt.[action] = @t_create then 5
1582
				else 100 end
1583
			,pt.[order]
1584
		) as current_step
1585
		,pt.[action] + ' ' + pt.[object desc] + ': ' + isnull(isnull(pt.[target parent object] + '.' + pt.[target object],pt.[target object]),isnull(pt.[source parent object] + '.' + pt.[source object],pt.[source object])) as step_desc
1586
		,pt.[sql text] as sql_text
1587
		,pt.[object desc]
1588
	from [#PIVOT_TABLE] as pt
1589
	where
1590
		pt.[action] <> ''
1591
		--ФИЛЬТР
1592
	option (maxdop 8)
1593

1594
fetch next from cursDB into @source_server, @db, @target_filegroup
1595
end
1596
close cursDB
1597
deallocate cursDB
1598

1599

1600

1601
if not exists (select * from #SCRIPT_TABLE)
1602
	print 'НЕТ ОБЪЕКТОВ ДЛЯ ОБНОВЛЕНИЯ !'
1603
else if @ops = 0
1604
begin--только генерация скрипта
1605

1606
	exec ('
1607
		create or alter proc #PrintingLongText
1608
			@text nvarchar(max)
1609
		as
1610
		set nocount on
1611
		declare
1612
			@pos int
1613
		
1614
		set @text = replace(replace(@text, char(13), char(10)), char(10) + char(10), char(10))
1615
		
1616
		while (1=1)
1617
		begin
1618
		    set @pos = charindex(char(10), @text)
1619
		
1620
			if (@pos = 0)
1621
			begin
1622
				print @text
1623
				break
1624
			end
1625
			else if (@pos > 4000)
1626
			begin
1627
			    set @text = stuff(@text, charindex('','', @text, 3900), 1, '','' + char(10))
1628
				continue
1629
			end
1630
		
1631
		    print left(@text, @pos - 1)
1632
		    
1633
			set @text = substring(@text, @pos + 1, len(@text))
1634
		end
1635
	')
1636

1637
	print '--СКРИПТ СОЗДАН: ' + format(getdate(), 'dd MMMM yyyy HH:mm')
1638
	print 'set nocount on'
1639
	print 'if left(@@servername, 12) <> ''' + left(@@servername, 12) + '''	throw 50000, ''СКРИПТ ПРЕДНАЗНАЧЕН ДЛЯ ДРУГОГО СЕРВЕРА !'', 1'
1640

1641
	select @message = string_agg('--' + upper(db) + '	 нет объектов для обновления', @c_nl) within group (order by l.db)
1642
	from #db_list as l
1643
	where
1644
		not exists (select * from #SCRIPT_TABLE as s where l.db = s.db)
1645
		and @@servername like '%' + l.trg_srv + '%'
1646

1647
	if @message is not null
1648
		print @message
1649

1650
	print 'if object_id(''tempdb..#toApply'',''U'') is null select current_step, db, cast('''' as nvarchar(max)) as result into #toApply from (values (null,null,null)'
1651
	print ''
1652
	print '--↓↓↓ УДАЛИТЬ ИЛИ ЗАКОММЕНТИРОВАТЬ НЕНУЖНЫЕ СТРОКИ НИЖЕ ↓↓↓'
1653

1654
	declare cursRW cursor for
1655
		select
1656
			current_step
1657
			,db
1658
			,step_desc
1659
		from #SCRIPT_TABLE
1660
		order by
1661
			db
1662
			,object_desc
1663
			,substring(step_desc, charindex(':', step_desc) + 1, len(step_desc))
1664
			,current_step
1665
	open cursRW
1666
	fetch next from cursRW into @current_step, @db, @step_desc
1667
	while @@fetch_status = 0
1668
	begin
1669
		set @step_desc = formatmessage('%-20s %s', trim(substring(@step_desc, 1, charindex(':', @step_desc))), trim(substring(@step_desc, charindex(':', @step_desc) + 1, len(@step_desc))))
1670

1671
		print formatmessage(',(%-4s,%-25s,%s)', @current_step, quotename(@db,''''), quotename(@step_desc,''''))
1672

1673
		fetch next from cursRW into @current_step, @db, @step_desc
1674
	end
1675
	close cursRW
1676
	deallocate cursRW
1677
	   
1678
	print '--↑↑↑ УДАЛИТЬ ИЛИ ЗАКОММЕНТИРОВАТЬ НЕНУЖНЫЕ СТРОКИ ВЫШЕ ↑↑↑
1679
	
1680
		) as t(current_step, db, step_desc)
1681
	else
1682
	begin
1683
		select * from #toApply where len([result]) > 5 order by db, current_step
1684

1685
		;throw 50000, ''СКРИПТ УЖЕ ЗАПУСКАЛСЯ, СГЕНЕРИРУЙТЕ НОВЫЙ !'', 1
1686
	end
1687

1688
	declare
1689
		@result int
1690
		,@message nvarchar(max)
1691

1692
	exec @result = sys.sp_getapplock
1693
	    @Resource = ''' + @applock_const + '''
1694
	    ,@LockMode = ''exclusive''
1695
	    ,@LockOwner = ''session''
1696
		,@LockTimeout = 0
1697
		,@DbPrincipal = ''' + @user + '''
1698
	
1699
	if @result < 0
1700
	begin
1701
	    select @message = string_agg(request_session_id, '','')
1702
		from sys.dm_tran_locks (nolock)
1703
		where
1704
			resource_type = ''APPLICATION''
1705
			and request_owner_type = ''SESSION''
1706
			and resource_description like ''%' + @applock_const + '%''
1707
	
1708
		set @message = concat(''ЗАКРОЙТЕ СЕССИИ: '', @message, '' !'')
1709
		
1710
		;throw 50000, @message, 1
1711
	end
1712
	'
1713
	
1714
	declare cursPR cursor for
1715
		select
1716
			db
1717
			,current_step
1718
			,step_desc
1719
			,sql_text
1720
		from [#SCRIPT_TABLE]
1721
		order by
1722
			db
1723
			,current_step
1724
	
1725
	open cursPR
1726
	fetch next from cursPR into @db, @current_step, @step_desc, @sql_text
1727
	
1728
	while (@@fetch_status = 0)
1729
	begin
1730
		print '--ДЕЙСТВИЕ: ' + upper(@step_desc)
1731
		print 'if exists (select 1 from #toApply where db = ''' + @db + ''' and current_step = ' + @current_step + ' and result = '''') '
1732
		print 'begin'
1733
		print 'use ' + quotename(@db)
1734
		print 'begin try'
1735
		print 'exec ('''
1736

1737
		if @step_desc like 'create trigger%'
1738
		begin
1739
			set @sql_text2 = replace(substring(@sql_text, charindex(@marker, @sql_text), charindex(@marker, @sql_text, charindex(@marker, @sql_text) +3) - charindex(@marker, @sql_text) + len(@marker)), @marker, '')
1740
			set @sql_text = stuff(@sql_text, charindex(@marker, @sql_text), charindex(@marker, @sql_text, charindex(@marker, @sql_text) +3) - charindex(@marker, @sql_text) + len(@marker), '')
1741
		end
1742
		
1743
		set @sql_text = replace(@sql_text,'''','''''')
1744

1745
		if len(@sql_text) < 4000
1746
			print @sql_text
1747
		else
1748
			exec #PrintingLongText @sql_text
1749

1750
		print ''')'
1751

1752
		if @step_desc like 'create trigger%'
1753
		begin
1754
			print 'exec ('''
1755
			print @sql_text2
1756
			print ''')'
1757
		end
1758

1759
		print 'update #toApply set [result] = ''OK'' where db = ''' + @db + ''' and current_step = ' + @current_step
1760
		print 'end try'
1761
		print 'begin catch'
1762
		print 'update #toApply set [result] = error_message() where db = ''' + @db + ''' and current_step = ' + @current_step
1763
		print 'end catch'
1764
		print 'end' + @c_nl
1765
	
1766
		fetch next from cursPR into @db, @current_step, @step_desc, @sql_text
1767
	end
1768
	
1769
	close cursPR
1770
	deallocate cursPR
1771
	
1772
	print '
1773
		if exists (select * from #toApply where len([result]) > 5)
1774
			select * from #toApply where len([result]) > 5 order by db, current_step
1775
		else
1776
			select ''СКРИПТ ВЫПОЛНИЛСЯ БЕЗ ОШИБОК !''	'
1777
	
1778
end
1779
else if @ops = 0
1780
begin--выполнение синхронизации метаданных
1781

1782
	print 'СТАРТ ВЫПОЛНЕНИЯ !'
1783

1784
	set @query = '
1785
		drop table if exists ' + @synch_result_tbl + '
1786
		create table ' + @synch_result_tbl + ' (
1787
			[db] sysname
1788
			,[step] int
1789
			,[desc] nvarchar(max)
1790
			,[error] nvarchar(max)
1791
			,[DTM] datetime default (getdate())
1792
		)
1793
	'
1794
	exec(@query)
1795

1796
	declare curs cursor for
1797
		select
1798
			db
1799
			,current_step
1800
			,step_desc
1801
			,sql_text
1802
		from [#SCRIPT_TABLE]
1803
		order by
1804
			db
1805
			,current_step
1806
	
1807
	open curs
1808
	fetch next from curs into @db, @current_step, @step_desc, @sql_text
1809
	
1810
	while (@@fetch_status = 0)
1811
	begin
1812
		begin try
1813

1814
			if @step_desc like 'create trigger%'
1815
			begin
1816
				set @sql_text2 = replace(substring(@sql_text, charindex(@marker, @sql_text), charindex(@marker, @sql_text, charindex(@marker, @sql_text) +3) - charindex(@marker, @sql_text) + len(@marker)), @marker, '')
1817
				set @sql_text = stuff(@sql_text, charindex(@marker, @sql_text), charindex(@marker, @sql_text, charindex(@marker, @sql_text) +3) - charindex(@marker, @sql_text) + len(@marker), '')
1818
				set @sql_text2 = 'use ' + quotename(@db) + ' exec(''' + @sql_text2 + ''')'
1819
			end
1820
			
1821
			set @sql_text = 'use ' + quotename(@db) + ' exec(''' + replace(@sql_text,'''','''''') + ''')'
1822
			
1823
			exec @sql_text
1824
	
1825
			if @step_desc like 'create trigger%'
1826
				exec (@sql_text2)
1827
			
1828
			set @result = 0
1829
		end try
1830
		begin catch
1831
			set @message = error_message()
1832
			set @query = '
1833
				insert into ' + @synch_result_tbl + ' ([db], [step], [desc], [error])
1834
				values (''' + @db + ''',''' + @current_step + ''',''' + @step_desc + ''',''' + @message + ''')'
1835
			exec (@query)
1836
			set @result = 1
1837
		end catch
1838

1839
		set @message = format(getdate(),'[HH:mm]') + ' [' + @current_step + '/' + cast(@@cursor_rows as varchar(10)) + '] ' + iif(@result = 0, '[OK] ', '[ERROR] ') + @step_desc
1840
		raiserror (@message, 10, 1) with nowait
1841

1842
		fetch next from curs into @db, @current_step, @step_desc, @sql_text
1843
	end
1844
	
1845
	close curs
1846
	deallocate curs
1847
	
1848
	set @query = '
1849
		if exists (select * from ' + @synch_result_tbl + ')
1850
		begin
1851
			print ''ВЫПОЛНЕНИЕ ЗАВЕРШИЛОСЬ С ОШИБКАМИ !''
1852
			select
1853
				[db] as [База данных]
1854
				,[step] as [Номер шага]
1855
				,[desc] as [Описание]
1856
				,[error] as [Текст ошибки]
1857
			from ' + @synch_result_tbl + '
1858
		end
1859
		else
1860
			print ''ВЫПОЛНЕНИЕ ЗАВЕРШИЛОСЬ БЕЗ ОШИБОК !''
1861
	'
1862
	exec (@query)
1863

1864
end
1865