SQL_scripts

1
create or alter procedure [dbo].[Comparison_of_definitions]
2
	@left_srv varchar(30) -- левый сервер
3
	,@right_srv varchar(30) -- правый сервер
4
	,@left_db varchar(30) -- левая база
5
	,@right_db varchar(30) -- правая база
6
	,@types varchar(30) -- KI=индексы, остальные типы это колонка type из sys.objects
7
	,@show_defin bit = 0 -- итоговый вывод с определения
8
	,@only_different bit = 0 -- в итоговом выводе только разчающиеся
9
as
10
set nocount on
11

12
declare
13
	@query nvarchar(max)
14
	,@db varchar(30)
15
	,@link varchar(30)
16
	,@l_srv_name varchar(30)
17
	,@r_srv_name varchar(30)
18
	,@srv_name varchar(30)
19

20
-- таблица маппинга линков и серверов
21
select @l_srv_name = [name]
22
from 
23
where [data_source] = @left_srv
24

25
select @r_srv_name = [name]
26
from 
27
where [data_source] = @right_srv
28

29

30
if (@l_srv_name = @r_srv_name and @right_db = @left_db)
31
	throw 50000, 'Одинаковые имена серверов и баз данных', 1
32

33
drop table if exists #R_result
34
drop table if exists #L_result
35

36
create table #R_result	(
37
	srv varchar(30)
38
	,db varchar(30)
39
	,shm varchar(30)
40
	,obj varchar(200)
41
	,tp varchar(30)
42
	,crdt datetime
43
	,mfdt datetime
44
	,def varchar(max)
45
	,chksum as checksum(upper(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(replace(replace(replace( SUBSTRING (def, CHARINDEX (obj, def), LEN(def)),char(32),''),char(9),''),char(10),''),char(13),''),'dbo',''),'].[',''),'[',''),']',''))) persisted
46
	,flnm as upper(concat(shm,'.',obj)) persisted
47
)
48
create table #L_result (
49
	srv varchar(30)
50
	,db varchar(30)
51
	,shm varchar(30)
52
	,obj varchar(200)
53
	,tp varchar(30)
54
	,crdt datetime
55
	,mfdt datetime
56
	,def varchar(max)
57
	,chksum as checksum(upper(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(replace(replace(replace( SUBSTRING (def, CHARINDEX (obj, def), LEN(def)),char(32),''),char(9),''),char(10),''),char(13),''),'dbo',''),'].[',''),'[',''),']',''))) persisted
58
	,flnm as upper(concat(shm,'.',obj)) persisted
59
)
60

61
select
62
	@db = @right_db
63
	,@link = @right_srv
64
	,@srv_name = @r_srv_name
65

66
begin try
67

68
while (1=1)
69
begin
70
	set @query = '
71
		use '+quotename(@db)+'
72

73
		' + iif(charindex('KI', @types) = 0, '', '
74
		select
75
			s.[schema name] as sch_name
76
			,s.[table name] + ''''.'''' + s.[index name] as obj_name
77
			,iif(s.[is primary key] | s.[is unique constraint] = 1,
78
					''''alter table '''' + s.[schema name] + ''''.'''' + s.[table name] + '''' add constraint '''' + s.[index name] + iif(s.[is primary key] = 1, '''' primary key '''', '''' unique '''') + s.[index type] + '''' ( '''' + s.[key column names] + '''' ) '''',
79
					''''create '''' + iif(s.[is unique] = 1,'''' unique '''', '''''''') + s.[index type] + '''' index '''' + s.[index name] + '''' on '''' + s.[schema name] + ''''.'''' + s.[table name] + '''' ( '''' + s.[key column names] + '''' ) ''''
80
				+ iif(s.[included column names] = '''''''', '''''''', concat('''' include ('''',s.[included column names],'''') ''''))
81
				+ iif(s.[has filter] = 1,'''' where '''' + s.[filter definition],'''''''')) as obj_def
82
			,create_date
83
			,modify_date
84
			,cast(case when s.[is primary key] = 1 then ''''PK'''' when s.[is unique constraint] = 1 then ''''UQ'''' else ''''IDX'''' end as char(2)) collate Latin1_General_CI_AS_KS_WS as obj_type
85
		into #ki
86
		from (
87
			select
88
				quotename(o.[name]) as [table name]
89
				,quotename(s.[name]) as [schema name]
90
				,quotename(i.[name]) as [index name]
91
				,stuff(ic.[key].value(''''/x[1]'''',''''nvarchar(max)''''),1,1,'''''''') collate Latin1_General_CI_AS_KS_WS as [key column names]
92
				,isnull(stuff(ic2.[incl].value(''''/x[1]'''',''''nvarchar(max)''''),1,1,''''''''),'''''''') collate Latin1_General_CI_AS_KS_WS as [included column names]
93
				,lower(i.[type_desc]) as [index type]
94
				,i.is_unique as [is unique]
95
				,i.has_filter as [has filter]
96
				,isnull(i.filter_definition,'''''''') as [filter definition]
97
				,i.is_primary_key as [is primary key]
98
				,i.is_unique_constraint as [is unique constraint]
99
				,o.create_date
100
				,o.modify_date
101
			from
102
				sys.indexes as i (nolock)
103
				inner join sys.objects as o (nolock)
104
					on i.object_id = o.object_id
105
					and o.is_ms_shipped = 0
106
				inner join sys.schemas as s (nolock)
107
					on s.schema_id = o.schema_id
108
				cross apply (
109
					select '''','''' + quotename(c.[name]) + iif(ic.is_descending_key = 0, '''' asc'''', '''' desc'''')
110
					from
111
						sys.index_columns as ic (nolock)
112
						inner join sys.columns as c (nolock)
113
							on c.object_id = ic.object_id
114
							and ic.column_id = c.column_id
115
					where
116
						ic.object_id = i.object_id
117
						and ic.index_id = i.index_id
118
						and ic.is_included_column = 0
119
					order by ic.key_ordinal
120
					for xml path(''''''''),root(''''x''''),type
121
				) ic([key])
122
				outer apply (
123
					select '''','''' + quotename(c.[name])
124
					from
125
						sys.index_columns as ic (nolock)
126
						inner join sys.columns as c (nolock)
127
							on c.object_id = ic.object_id
128
							and ic.column_id = c.column_id
129
					where
130
						ic.object_id = i.object_id
131
						and ic.index_id = i.index_id
132
						and ic.is_included_column = 1
133
					order by ic.key_ordinal
134
					for xml path(''''''''),root(''''x''''),type
135
				) ic2(incl)
136
			where i.[type_desc] <> ''''heap''''
137
		) as s') + '
138

139
		;with s as (
140
			select
141
				s.name as sch_name
142
				,o.name as obj_name
143
				,o.type
144
				,o.create_date
145
				,o.modify_date
146
				,object_definition(o.object_id) as obj_def
147
			from
148
				sys.objects as o (nolock)
149
				inner join sys.schemas as s (nolock)
150
					on s.schema_id = o.schema_id
151
				inner join string_split('''''+replace(@types,',','')+''''','''';'''') as ss
152
					on o.type = ss.value
153
		' + iif(charindex('KI', @types) = 0, '', '
154
			union all
155
			select
156
				sch_name
157
				,obj_name
158
				,obj_type
159
				,create_date
160
				,modify_date
161
				,obj_def
162
			from #ki
163
		')+ ')
164
		select
165
			'''''+@srv_name+'''''
166
			,'''''+@db+'''''
167
			,sch_name
168
			,obj_name
169
			,type
170
			,create_date
171
			,modify_date
172
			,rtrim(ltrim(isnull(obj_def,''''НЕТ ОПРЕДЕЛЕНИЯ (возможно проблема с правами или линком)''''))) as def
173
		from s
174
		'
175

176
	set @query = 'exec ('''+@query+''') at '+quotename(@link)
177

178
	if (@link = @right_srv)
179
		insert into #R_result (srv,db,shm,obj,tp,crdt,mfdt,def)
180
		exec @query
181
	else
182
	begin
183
		insert into #L_result (srv,db,shm,obj,tp,crdt,mfdt,def)
184
		exec @query
185

186
		break
187
	end
188

189
	select @db = @left_db, @link = @left_srv, @srv_name = @l_srv_name
190
end
191
end try
192
begin catch
193
	select error_message()
194
	throw;
195

196
end catch
197

198
;with cte as (
199
	select
200
		format(t.mfdt,'dd.MM.yyyy HH:mm') as ldtmf
201
		,t.obj as lobj
202
		,isnull(t.tp,t2.tp) as tp
203
		,isnull(t.shm,t2.shm) as shm
204
		,t2.obj as robj
205
		,format(t2.mfdt,'dd.MM.yyyy HH:mm') as rdtmf
206
		,iif(@show_defin=0,'', case
207
								when t.flnm = t2.flnm and t.tp = t2.tp and t.chksum = t2.chksum
208
								then t.def
209
								when t.flnm = t2.flnm and t.tp = t2.tp and t.chksum <> t2.chksum
210
								then concat('ЛЕВОЕ:',char(13)+char(10),t.def,char(10)+char(13),'ПРАВОЕ:',char(13)+char(10),t2.def)
211
								else isnull(t.def,t2.def) end) as def
212
		,t.def as l_def
213
		,t2.def as r_def
214
		,case when t.flnm = t2.flnm and t.tp = t2.tp and t.chksum = t2.chksum then 1
215
				when t.flnm = t2.flnm and t.tp = t2.tp and t.chksum <> t2.chksum then 2
216
				else 3 end as dif
217
	from
218
		#L_result as t
219
		full join #R_result as t2
220
			on t.flnm = t2.flnm
221
			and t.tp = t2.tp
222
			--and t.chksum = t2.chksum
223
)
224
select *
225
from cte
226
where (@only_different = 0 or dif > 1)
227
order by row_number() over(order by (case when lobj is not null and robj is not null then 1
228
									when lobj is not null and robj is null then 2
229
									else 3 end), tp, shm, lobj, robj)
230

231

232
go
233

234