dbo.QBM_PCustomSQLDependencyFill

1   create   procedure QBM_PCustomSQLDependencyFill as begin declare @Scriptname nvarchar(255) declare @SQLOrig nvarchar(max) declare @SQLRepl nvarchar
2(max) declare @Posi int declare @merken table (UID_QBMCustomSQL varchar(38) collate database_default , scriptname nvarchar(255) collate database_default
3, countDeps int, SortOrder int , ScriptCode nvarchar(max)collate database_default, primary key (UID_QBMCustomSQL) ) declare @work table (UID_QBMCustomSQL
4 varchar(38) collate database_default , scriptname nvarchar(255) collate database_default, countDeps int, SortOrder int , ScriptCode nvarchar(max)collate
5 database_default, primary key (UID_QBMCustomSQL) ) declare @lauf int declare @AnzahlObjekte int declare @CountDeps_neu int declare @Sortorder_neu int 
6declare @Geaendert int declare @Schema_Id int declare @AllesOK int  declare @UID_QBMCustomSQL varchar(38) declare @DebugSwitch int = 0 declaRE @CountItems
7 INT declare @XUser nvarchar(64) = object_name(@@procid) declare @Xdate datetime = getutcdate() SET XACT_ABORT OFF BEGIN TRY select @AllesOK = 1 select
8 top 1 @Schema_Id = schema_id from sys.schemas where name = N'dbo' if @DebugSwitch > 0 begin print 'Start ' + convert(varchar(64), getutcdate(), 121) end
9 insert into @merken(UID_QBMCustomSQL, scriptname, CountDeps, SortOrder) select max(cs.uid_QBMCustomSQL), cs.scriptname, isnull(count(distinct v.object_id
10), 0), 1 from QBMCustomSQL cs join sys.objects s on cs.scriptname = s.name collate database_default left outer join sys.sql_dependencies d on s.object_id
11 = d.object_id left outer join sys.objects v  on d.referenced_major_id = v.object_id left outer join QBMCustomSQL vs on v.name = vs.Scriptname collate 
12database_default and v.schema_id = @Schema_Id where cs.scriptname <> isnull(vs.scriptname, N'') and s.schema_id = @Schema_Id and cs.xtouched <> 'F' and
13 cs.scripttype in ('P', 'F', 'V', 'T', 'N') and cs.scriptname not in ('QBM_ZCustomSQLFill', OBJECT_NAME(@@procid)) group by cs.scriptname select @AnzahlObjekte
14 = count(*) from @merken if @DebugSwitch > 0 begin print '@merken gefüllt ' + convert(varchar(64), getutcdate(), 121) end update @merken set ScriptCode
15 = dbo.QBM_FSQRemoveComment(sc.Scriptcode) from @merken m join qbmCustomsql sc on sc.UID_QBMCustomSQL = m.UID_QBMCustomSQL update @merken set SortOrder
16 = patindex(N'%create[ ' + nchar(9) + N']%', Scriptcode) if @DebugSwitch > 0 begin print '@merken dekommentiert ' + convert(varchar(64), getutcdate(), 
17121) end delete  @merken where isnull(SortOrder, 0) = 0 select @CountItems = @@ROWCOUNT if @DebugSwitch > 0 begin print 'sätze ohne create gelöscht ' +
18 str(@CountItems) end update @merken set ScriptCode = case  when SortOrder = 1 then N'alter' + substring(ScriptCode, 7, 1000000) else  substring(ScriptCode
19,1, SortOrder -1)+ N'alter' + substring(ScriptCode, @posi+6, 1000000) end update @merken set SortOrder = 1 if @DebugSwitch > 0 begin print '@merken mit alter '
20 + convert(varchar(64), getutcdate(), 121) end select @lauf = 1 while @lauf <= @AnzahlObjekte * 2 and @AllesOK = 1 begin select @geaendert = 0 if @DebugSwitch
21 > 0 begin print '####################### neuer Durchlauf '+str(@lauf) + str(@AnzahlObjekte) end  delete  @work  insert @work(ScriptCode, SortOrder, UID_QBMCustomSQL
22, countDeps, scriptname) select ScriptCode, SortOrder, UID_QBMCustomSQL, countDeps, scriptname from @merken m  while exists (select top 1 1 from @work 
23) and @AllesOK = 1 BEGIN select top 1 @UID_QBMCustomSQL = w.UID_QBMCustomSQL , @Scriptname = w.scriptname , @SQLOrig = w.ScriptCode from @work w order 
24by w.SortOrder, w.scriptname desc if @DebugSwitch > 0 begin print @Scriptname end  BEGIN TRY BEGIN TRY exec QBM_PExecuteSQLWithRetry_LLP @SQLStatement 
25= @SQLRepl , @LockTimeout_ms = 50 , @MaxWaitTimeForLock_s = 2 , @ProcIDForJournal = @@procid , @HandleErrorSilent = 0 exec QBM_PSessionErrorClean END TRY
26 BEGIN CATCH  if @DebugSwitch > 0 begin print @scriptname print isnull(ERROR_MESSAGE() , '<no message>') end update qbmCustomSQL set XTouched = 'F' , XDateUpdated
27 = @Xdate , XUserUpdated = @XUser where UID_QBMCustomSQL = @UID_QBMCustomSQL delete  @merken where UID_QBMCustomSQL = @UID_QBMCustomSQL continue  END CATCH
28 END TRY BEGIN CATCH  select @AllesOK = 0 END CATCH  if @AllesOK = 0 begin continue end select @CountDeps_neu = null select @CountDeps_neu = isnull(count
29(distinct v.object_id), 0) from sys.objects s left outer join sys.sql_dependencies d on s.object_id = d.object_id left outer join sys.objects v  on d.referenced_major_id
30 = v.object_id left outer join QBMCustomSQL vs on v.name = vs.Scriptname collate database_default where s.name = @Scriptname and vs.UID_QBMCustomSQL <>
31 @UID_QBMCustomSQL and s.schema_id = @Schema_Id and v.schema_id = @Schema_Id group by s.object_id select @CountDeps_neu = isnull(@CountDeps_neu, 0) select
32 @Sortorder_neu = null select @Sortorder_neu = isnull(max( vm.SortOrder),0) + 1 from sys.objects s left outer join sys.sql_dependencies d on s.object_id
33 = d.object_id left outer join sys.objects v  on d.referenced_major_id = v.object_id left outer join QBMCustomSQL vs on v.name = vs.Scriptname collate 
34database_default left outer join @merken vm on vs.UID_QBMCustomSQL = vm.UID_QBMCustomSQL collate database_default where s.name = @Scriptname and vm.scriptname
35 <> @Scriptname and s.schema_id = @Schema_Id and v.schema_id = @Schema_Id group by s.name select @Sortorder_neu = isnull(@Sortorder_neu, 1) if @DebugSwitch
36 > 0 begin print @scriptname + str(@CountDeps_neu) + str(@Sortorder_neu) end update @merken set countDeps = @CountDeps_neu where UID_QBMCustomSQL = @UID_QBMCustomSQL
37 and countDeps <> @CountDeps_neu if @@rowcount > 0 begin if @DebugSwitch > 0 begin print 'count deps geändert' end select @geaendert = 1 end update @merken
38 set SortOrder = @Sortorder_neu where UID_QBMCustomSQL = @UID_QBMCustomSQL and SortOrder < @Sortorder_neu if @@rowcount > 0 begin if @DebugSwitch > 0 begin
39 print 'SortOrder  geändert' end select @geaendert = 1 end delete  @work where UID_QBMCustomSQL = @UID_QBMCustomSQL END  if @AllesOK = 0 begin continue
40 end if @geaendert = 1 begin select @lauf = @lauf +1 end else begin select @lauf = @AnzahlObjekte * 2 +1 end end  update QBMCustomSQl set SortOrder = m.SortOrder
41 , XDateUpdated = @Xdate , XUserUpdated = @XUser from QBMCustomSQl cu join @merken m on cu.UID_QBMCustomSQl = m.UID_QBMCustomSQL where m.SortOrder <> ISNULL
42(cu.SortOrder, 0) if @DebugSwitch > 0 begin select s.SortOrder, s.ScriptType, s.ScriptName from QBMCustomSQL s order by s.SortOrder end END TRY BEGIN CATCH
43 exec QBM_PSessionErrorAdd default declare @Rethrow varchar(1000) = dbo.QBM_FGISessionErrorRethrow() RAISERROR (@Rethrow, 18, 1) WITH NOWAIT END CATCH 
44end 
45