dbo.QBM_ZPrePropTable

1    create   procedure QBM_ZPrePropTable (@SlotNumber int) as begin  declare @GenProcID varchar(38) declare @TableName varchar(38) declare @CountItems
2 int  declare @SQLcmd nvarchar(max) declare @subject nvarchar(1000) declare @rowcnt int declare @DebugLevel char(1) = 'W' declare @GenProcID_R varchar(38
3) = dbo.QBM_FGISessionContext('') declare @ElementBuffer QBM_YCursorBuffer declare @ElementCount int declare @ElementIndex int declare @DBQueueCurrent 
4QBM_YDBQueueCurrent declare @XUser nvarchar(64) = object_name(@@procid) declare @Xdate datetime = getutcdate() BEGIN TRY insert into @DBQueueCurrent(UID_DialogDBQueue
5, UID_Parameter, UID_SubParameter, GenProcID) select UID_DialogDBQueue, UID_Parameter, UID_SubParameter, GenProcID from QBMDBQueueCurrent cu with (readpast
6) where cu.SlotNumber = @SlotNumber if @@rowcount = 0 begin goto EndLabel end select @GenProcID = max(GenProcID) from @DBQueueCurrent where GenProcID >
7 ' ' if isnull(@Genprocid, '') = '' begin select @GenProcID = newid() end declare @PrePropHelper QBM_YPrepropHelper insert into @PrePropHelper (uid_parameter
8 , IsDeactivated , IsDeactivatedOld, GenProcID , PreProcessorCondition) select x.uid_parameter, 0 + isnull(b.isDeactivatedByPreProcessor, 0), t.isDeactivatedByPreProcessor
9 , x.GenProcID, isnull(t.PreProcessorCondition,'') from @DBQueueCurrent x join dialogtable t on x.uid_parameter = t.TableName left outer join dialogtable
10 b on t.UID_DialogTableBase = b.UID_DialogTable    update @PrePropHelper set IsDeactivated = dbo.QBM_FGIPrepropConditionDeactiv(h.PreProcessorCondition
11) from @PrePropHelper h where h.PreProcessorCondition > ' '  if exists (select top 1 1 from DialogTable , @PrePropHelper x where DialogTable.TableName 
12= x.uid_parameter and isnull(DialogTable.IsDeactivatedByPreProcessor,0) <> x.IsDeactivated  and dialogtable.TableName not in (select childtable from QBM_VQBMRelation
13 where isMNRelation = 1) ) begin   exec QBM_PDBQueueInsert_WaitForComp 'Table', @GenProcID  end  exec QBM_PSessionContextSet 'GenProcID', @GenProcID update
14 DialogTable set IsDeactivatedByPreProcessor = x.isDeactivated , XDateUpdated = @Xdate , XUserUpdated = @XUser  from DialogTable r join @PrePropHelper 
15x on r.TableName = x.uid_parameter where (r.IsDeactivatedByPreProcessor <> x.IsDeactivated or x.IsDeactivated = 1 ) select @CountItems = @@rowcount   declare
16 @DBQueueElements_01 QBM_YDBQueueRaw insert into @DBQueueElements_01 (object, subobject, genprocid) select x.uid, null, @GenProcID from (select distinct
17 t.TableName as UID, x.GenProcID from Dialogtable t join DialogTable b on t.UID_DialogTableBase = b.UID_DialogTable join @PrePropHelper x on b.TableName
18 = x.uid_parameter and ( x.IsDeactivated <>x.IsDeactivatedOld or x.IsDeactivated = 1 ) ) as x exec QBM_PDBQueueInsert_Bulk 'QBM-K-CommonPrepropTable', 
19@DBQueueElements_01  declare @DBQueueElements_02 QBM_YDBQueueRaw insert into @DBQueueElements_02 (object, subobject, genprocid) select x.UID, null, x.GenProcID
20 from (select distinct c.UID_DialogColumn as UID, x.GenProcID from DialogColumn c join DialogTable t on c.UID_DialogTable = t.UID_DialogTable join @PrePropHelper
21 x on t.TableName = x.uid_parameter  and ( x.IsDeactivated <>x.IsDeactivatedOld or c.IsDeactivatedByPreprocessor <> x.IsDeactivated or c.IsPKMember = 1
22 ) ) as x exec QBM_PDBQueueInsert_Bulk 'QBM-K-CommonPrepropColumn', @DBQueueElements_02  declare @DBQueueElements_03 QBM_YDBQueueRaw insert into @DBQueueElements_03
23 (object, subobject, genprocid) select x.UID, null, x.GenProcID from (select distinct r.UID_ChildColumn as uid, x.GenProcID  from QBM_VQBMRelation r join
24 @PrePropHelper x on r.ParentTable = x.uid_parameter and r.ChildTable <> r.ParentTable join DialogColumn c on r.UID_ChildColumn = c.UID_DialogColumn where
25 ( x.IsDeactivated <> x.IsDeactivatedOld or c.IsDeactivatedByPreprocessor <> x.IsDeactivated ) ) as x exec QBM_PDBQueueInsert_Bulk 'QBM-K-CommonPrepropColumnFK'
26, @DBQueueElements_03  declare @DBQueueElements_04 QBM_YDBQueueRaw insert into @DBQueueElements_04 (object, subobject, genprocid) select x.uid, null, @GenProcID
27 from (select distinct y.UID_QBMTree as UID, x.GenProcID from (select t.uid_QBMTree , ta.TableName from QBMTree t join DialogTable ta on t.UID_DialogTable
28 = ta.UID_DialogTable where ta.TableName > ' ' ) as y join @PrePropHelper x on y.TableName = x.uid_parameter join QBMTree t on t.uid_QBMTree = y.uid_QBMTree
29 where ( x.IsDeactivated <>x.IsDeactivatedOld or t.IsDeactivatedByPreprocessor <> x.IsDeactivated ) ) as x exec QBM_PDBQueueInsert_Bulk 'QBM-K-CommonPrepropTree'
30, @DBQueueElements_04 declare @DBQueueElements_04Result QBM_YDBQueueRaw insert into @DBQueueElements_04Result (object, subobject, genprocid) select x.uid
31, null, @GenProcID from (select distinct y.UID_QBMTreeResult as UID, x.GenProcID from (select t.uid_QBMTreeResult, ta.TableName from QBMTreeResult t join
32 Dialogobject o on t.uid_DialogObject = o.uid_DialogObject join DialogTable ta on o.UID_DialogTable = ta.UID_DialogTable ) as y join @PrePropHelper x on
33 y.TableName = x.uid_parameter join QBMTreeResult t on t.uid_QBMTreeResult = y.uid_QBMTreeResult where ( x.IsDeactivated <>x.IsDeactivatedOld or t.IsDeactivatedByPreprocessor
34 <> x.IsDeactivated ) ) as x exec QBM_PDBQueueInsert_Bulk 'QBM-K-CommonPrepropTreeResult', @DBQueueElements_04Result  declare @DBQueueElements_05 QBM_YDBQueueRaw
35 insert into @DBQueueElements_05 (object, subobject, genprocid) select x.uid, null, @GenProcID from (select distinct c.UID_Jobchain as UID, x.GenProcID
36 from Jobchain c join DialogTable ta on c.UID_DialogTable = ta.UID_DialogTable join @PrePropHelper x on ta.TableName = x.uid_parameter and ( x.IsDeactivated
37 <>x.IsDeactivatedOld or c.IsDeactivatedByPreprocessor <> x.IsDeactivated ) ) as x exec QBM_PDBQueueInsert_Bulk 'QBM-K-CommonPrepropJobChain', @DBQueueElements_05
38  update DialogColumn set IsDeactivatedByPreProcessor = 1 , XDateUpdated = @Xdate , XUserUpdated = @XUser from DialogColumn c join DialogTable t on c.UID_DialogTable
39 = t.UID_DialogTable join @PrePropHelper x on x.UID_Parameter = t.TableName where x.IsDeactivated = 1 and c.IsDeactivatedByPreProcessor = 0  if @CountItems
40 > 0  begin  declare @DBQueueElements_06 QBM_YDBQueueRaw insert into @DBQueueElements_06 (object, subobject, genprocid) select x.UID, null, x.GenProcID
41 from (  select t.UID_DialogTable as UID, x.GenProcID from Dialogtable t join DialogTable b on t.UID_DialogTableBase = b.UID_DialogTable join @PrePropHelper
42 x on b.TableName = x.uid_parameter and ( x.IsDeactivated <>x.IsDeactivatedOld or x.IsDeactivated = 1 ) union  select p.UID_DialogTable as UID, x.GenProcID
43 from Dialogtable t join dialogtable p on t.UID_DialogTableUnion = p.UID_DialogTable join @PrePropHelper x on t.TableName = x.uid_parameter and ( x.IsDeactivated
44 <>x.IsDeactivatedOld or x.IsDeactivated = 1 )  where t.UID_DialogTableUnion > ' ' ) as x exec QBM_PDBQueueInsert_Bulk 'QBM-K-CommonRebuildView', @DBQueueElements_06
45       select @SQLcmd = N'' insert into @ElementBuffer (Ident1, Int1, UID1) select t.TableName , i.rowcnt , x.GenProcID from dialogtable t with (readpast
46) join @PrePropHelper x on t.TableName = x.uid_parameter and t.isdeactivatedbypreprocessor = 1  and isnull(x.IsDeactivated,0) <> isnull(x.IsDeactivatedOld
47,0) join sys.objects o with (readpast) on t.TableName = o.name  join (select object_id, avg(rows) as rowcnt from ( select object_id, avg(rows) as rows 
48from sys.partitions with (readpast) group by object_id, index_id ) as x group by object_id ) as i on o.object_id = i.object_id and i.rowcnt > 0  order 
49by t.TableName select @ElementCount = @@ROWCOUNT select @ElementIndex = 1 while @ElementIndex <= @ElementCount begin select top 1 @TableName = bu.Ident1
50 , @rowcnt = bu.Int1 , @GenProcID = bu.UID1 from @ElementBuffer bu where bu.ElementIndex = @ElementIndex select @SQLcmd = N'TableName' + nchar(9) + N'Rowcount'
51 + nchar(13)+nchar(10) +replicate(N'-',40) select @SQLcmd = @SQLcmd + nchar(13) + nchar(10) + @TableName + nchar(9) + cast(@rowcnt as nvarchar(16)) select
52 @subject = N'There are deactivated tables with data! (' + isnull(@@servername,N'') + N'.' + isnull(db_name(),N'') + N')' + @TableName + nchar(9) + cast
53(@rowcnt as nvarchar(16)) exec QBM_PJournal @subject, @@procid, 'D', @DebugLevel if dbo.QBM_FGIConfigparmValue('Common\MailNotification') > ' ' begin exec
54 QBM_PJobCreate_SendMail @subject , @Message = @SQLcmd, @GenProcID = @GenProcID, @checkForExisting = 1 , @AdditionalObjectKeysAffected = DEFAULT end select
55 @ElementIndex += 1 end       declare @DBQueueElements_07 QBM_YDBQueueRaw insert into @DBQueueElements_07 (object, subobject, genprocid) select x.uid, 
56null, @GenProcID from (select distinct o.UID_DialogObject as UID, x.GenProcID from DialogObject o join DialogTable t on o.UID_DialogTable = t.UID_DialogTable
57 join @PrePropHelper x on t.TableName = x.uid_parameter and x.IsDeactivated <>x.IsDeactivatedOld ) as x exec QBM_PDBQueueInsert_Bulk 'QBM-K-CommonPrepropObject'
58, @DBQueueElements_07    select @GenProcID = max(GenProcID) from @DBQueueCurrent where GenProcID > ' '  if isnull(@GenProcID, '') = '' begin select @GenProcID
59 = newid() end    declare @DBQueueElements_08 QBM_YDBQueueRaw insert into @DBQueueElements_08 (object, subobject, genprocid) select x.UID, null, x.GenProcID
60 from (select x.uid_parameter as UID, x.GenProcID from @PrePropHelper x where x.IsDeactivated <>x.IsDeactivatedOld ) as x exec QBM_PDBQueueInsert_Bulk 
61'QBM-K-CommonMakeWatchtrigger', @DBQueueElements_08  end   END TRY BEGIN CATCH exec QBM_PSessionErrorAdd default RAISERROR ('', 18, 1) WITH NOWAIT END 
62CATCH endLabel: exec QBM_PSessionContextSet 'GenProcID', @GenProcID_R return end 
63