Back to OIM Explorer

dbo.QBM_PForeignKeyWrongRepair

Stored ProcedureSQL_STORED_PROCEDURESandbox DB

Stored Procedure.

Source: sandbox-db sys.sql_modules

Source size: 30.198 characters

Interpretation

  • Database routine. Review parameters, called procedures, DBQueue inserts, and QBM_PJobCreate helper calls before assuming side effects.

Relations

  • No extracted relations.

Typed Edges

  • references source dbo.QBM_FCVGUIDToModuleOwner source text reference
  • references source dbo.QBM_FCVObjectKeyToModuleOwner source text reference
  • references source dbo.QBM_FGIDBOwner source text reference
  • references source dbo.QBM_FGIModuleExists source text reference
  • references source dbo.QBM_FGISessionErrorRethrow source text reference
  • references source dbo.QBM_PExecuteSQLWithRetry_LLP source text reference
  • references source dbo.QBM_PForeignKeyEmptySetNull source text reference
  • references source dbo.QBM_PSessionErrorAdd source text reference

References

Referenced By

Complete Source

SQL1.053 lines
1CREATE PROCEDURE QBM_PForeignKeyWrongRepair(2  @ChildTablePattern varchar(32) = '%',3  @ChildColumnPattern varchar(32) = '%',4  @ParentTablePattern varchar(32) = '%',5  @ParentColumnPattern varchar(32) = '%',6  @RepairOnlyNonExistingModule BIT = 0,7  @OwnerOfParent varchar(3) = '%',8  @RepairFromReport BIT = 0,9  @FilterByModuleGuidAllowed BIT = 0,10  @CheckOutsideValues BIT = 0,11  @MasterMigMode BIT = 012)13AS14BEGIN15  DECLARE @ParentTable nvarchar(64),16  @ChildTable nvarchar(64),17  @ParentColumn nvarchar(64),18  @ChildColumn nvarchar(64),19  @RepairMethod nvarchar(16)20  DECLARE @ParentTableBase varchar(64)21  DECLARE @ParentRestriction nvarchar(16)22  DECLARE @isNullableColumn nvarchar(10)23  DECLARE @isPKMember BIT24  DECLARE @changed BIT25  DECLARE @changedTotal BIT26  DECLARE @CountItems int27  DECLARE @CmdWork nvarchar(max)28  DECLARE @CmdDelete nvarchar(max)29  DECLARE @CmdSetNull nvarchar(max)30  DECLARE @msg nvarchar(1000)31  DECLARE @IsDynamicFK int32  DECLARE @HasXObjectKey BIT33  DECLARE @ChangesForeign int = 034  DECLARE @DebugSwitch int = 035  DECLARE @ElementCount int36  DECLARE @ElementIndex int37  DECLARE @ElementLast int38  DECLARE @ElementBufferMulti QBM_YCursorBuffer39  DECLARE @ElementCountOutsideValues int40  DECLARE @ElementIndexOutsideValues int41  DECLARE @ElementLastOutsideValues int42  DECLARE @ElementBufferOutsideValues QBM_YCursorBuffer43  DECLARE @SQLOutsideValues nvarchar(max)44  DECLARE @WerOutsideValues QBM_YParameterList45  SET XACT_ABORT OFF46  BEGIN TRY47    SET nocount48      ON drop TABLE49    IF EXISTS #TablesForOutside50    CREATE TABLE #TablesForOutside(51      KeyPattern varchar(138) collate database_default NOT NULL primary key52    )53    INSERT INTO #TablesForOutside(KeyPattern54  )55  SELECT '<Key><T>' + t.TableName + '</T>'56  FROM DialogTable t57  IF @RepairFromReport = 058  BEGIN59    EXEC QBM_PForeignKeyEmptySetNull @ChildTablePattern60  END61  DECLARE @ModCheck38X nvarchar(max62) = ' dbo.QBM_FCVObjectKeyToModuleOwner(@ChildTable.XObjectKey) '63DECLARE @ModCheck38K nvarchar(max64) = ' case dbo.QBM_FCVGUIDToModuleOwner(@ChildTable.@Childcolumn)  when ''ZZZ'' then left(@ChildTable.@Childcolumn, 3) else dbo.QBM_FCVGUIDToModuleOwner(@ChildTable.@Childcolumn) end '65DECLARE @ModCheck138 nvarchar(max66) = ' dbo.QBM_FCVObjectKeyToModuleOwner(@ChildTable.@Childcolumn) '67SELECT68  @cmdDelete = CONCAT('69if exists (select top 1 170			from @ChildTable with (readpast)71%report%					join QBMForeignKeyWrongReport fkwr on @ChildTable.@ChildColumn = fkwr.InvalidValue72			where not exists (select top 1 173								 from @ParentTable p  with (readpast)74								where p.@Parentcolumn = @ChildTable.@Childcolumn75							) 76			 and @ChildTable.@Childcolumn > '' ''77%Mamig%		 and @ChildTable.XObjectKey like ''<Key><T>@ChildTable</T><P>[a-z][0-z][0-z]-%'' 78%dyn%		 and @ChildTable.@Childcolumn like ''<Key><T>@ParentTable</T>%'' 79'80  ,81CASE82  WHEN @OwnerOfParent = '%' THEN83''84ELSE '85%dyn%		 and dbo.QBM_FCVObjectKeyToModuleOwner(@ChildTable.@ChildColumn) like ''@OwnerOfParent''86%uid%		 and left(@ChildTable.@ChildColumn, 3) like ''@OwnerOfParent''87'88END,89'90		)91begin92%Xori%	update @ChildTable 93%Xori%		set XOrigin = 094%Xori%	  from @ChildTable 95%Xori%%report%					join QBMForeignKeyWrongReport fkwr on @ChildTable.@ChildColumn = fkwr.InvalidValue96%Xori%		where not exists (select top 1 197%Xori%							 from @ParentTable p  with (readpast)98%Xori%							where p.@Parentcolumn = @ChildTable.@Childcolumn99%Xori%						) 100%Xori%	-- und der Childschlssel ist nicht leer101%Xori%		 and @ChildTable.@Childcolumn > '' ''102%Xori%%dyn%		 and @ChildTable.@Childcolumn like ''<Key><T>@ParentTable</T>%'' 103%Xori%%Mod%	and dbo.QBM_FGIModuleExists(%modcheck%) = 0104'105,106CASE107  WHEN @OwnerOfParent = '%' THEN108''109ELSE '110%Xori%%dyn%		 and dbo.QBM_FCVObjectKeyToModuleOwner(@ChildTable.@ChildColumn) like ''@OwnerOfParent''111%Xori%%uid%		 and left(@ChildTable.@ChildColumn, 3) like ''@OwnerOfParent''112'113END,114'115116	delete /* Platzhalter, keine Variable */ @ChildTable117		from @ChildTable118%report%					join QBMForeignKeyWrongReport fkwr on @ChildTable.@ChildColumn = fkwr.InvalidValue119		where not exists (select top 1 1120							 from @ParentTable p   with (readpast)121							where p.@Parentcolumn = @ChildTable.@Childcolumn122						) 123	-- und der Childschlssel ist nicht leer124		 and @ChildTable.@Childcolumn > '' ''125%dyn%		 and @ChildTable.@Childcolumn like ''<Key><T>@ParentTable</T>%'' 126'127,128CASE129  WHEN @OwnerOfParent = '%' THEN130''131ELSE '132%dyn%		 and dbo.QBM_FCVObjectKeyToModuleOwner(@ChildTable.@ChildColumn) like ''@OwnerOfParent''133%uid%		 and left(@ChildTable.@ChildColumn, 3) like ''@OwnerOfParent''134'135END,136'137138%Mod%	and dbo.QBM_FGIModuleExists(%modcheck%) = 0139%Mamig%		and @ChildTable.XObjectKey like ''<Key><T>@ChildTable</T><P>[a-z][0-z][0-z]-%'' 140end141'142)143DECLARE @cmdDeleteDynamicOutOfValid nvarchar(Max144) = CONCAT('145if exists (select top 1 1146			from @ChildTable  with (readpast)147%report%					join QBMForeignKeyWrongReport fkwr on @ChildTable.@ChildColumn = fkwr.InvalidValue148						left outer join (149										select tp.TableName150										from DialogValidDynamicRef vr with (readpast) join DialogColumn c with (readpast) on vr.UID_DialogColumn = c.UID_DialogColumn151																	join DialogTable t with (readpast) on c.UID_DialogTable = t.UID_DialogTable152																	join DialogTable tp with (readpast) on vr.UID_DialogTableReference = tp.UID_DialogTable153										where t.TableName = ''@ChildTable''154										 and c.ColumnName = ''@ChildColumn''155										) as y on @ChildTable.@ChildColumn like ''<Key><T>'' + y.TableName + ''</T>%'' 156			where y.TableName is null157%Mamig%		and @ChildTable.XObjectKey like ''<Key><T>@ChildTable</T><P>[a-z][0-z][0-z]-%'' 158			'159,160CASE161  WHEN @OwnerOfParent = '%' THEN162''163ELSE '164			and dbo.QBM_FCVObjectKeyToModuleOwner(@ChildTable.@ChildColumn) like ''@OwnerOfParent''165'166END,167'168		)169begin170%Xori%	update @ChildTable171%Xori%		set XOrigin = 0172%Xori%		from @ChildTable 173%Xori%%report%					join QBMForeignKeyWrongReport fkwr on @ChildTable.@ChildColumn = fkwr.InvalidValue174%Xori%					left outer join (175%Xori%									select tp.TableName176%Xori%									from DialogValidDynamicRef vr with (readpast) join DialogColumn c with (readpast) on vr.UID_DialogColumn = c.UID_DialogColumn177%Xori%																join DialogTable t with (readpast) on c.UID_DialogTable = t.UID_DialogTable178%Xori%																join DialogTable tp with (readpast) on vr.UID_DialogTableReference = tp.UID_DialogTable179%Xori%									where t.TableName = ''@ChildTable''180%Xori%									 and c.ColumnName = ''@ChildColumn''181%Xori%									) as y on @ChildTable.@ChildColumn like ''<Key><T>'' + y.TableName + ''</T>%'' 182%Xori%		where y.TableName is null183%Xori%		and @ChildTable.@ChildColumn > '' ''184%Xori%%Mod%	and dbo.QBM_FGIModuleExists(%modcheck%) = 0185'186,187CASE188  WHEN @OwnerOfParent = '%' THEN189''190ELSE '191%Xori%		and dbo.QBM_FCVObjectKeyToModuleOwner(@ChildTable.@ChildColumn) like ''@OwnerOfParent''192'193END,194'195196	delete /* Platzhalter, keine Variable */ @ChildTable197	from @ChildTable 198%report%					join QBMForeignKeyWrongReport fkwr on @ChildTable.@ChildColumn = fkwr.InvalidValue199				left outer join (200								select tp.TableName201								from DialogValidDynamicRef vr with (readpast) join DialogColumn c with (readpast) on vr.UID_DialogColumn = c.UID_DialogColumn202															join DialogTable t with (readpast) on c.UID_DialogTable = t.UID_DialogTable203															join DialogTable tp with (readpast) on vr.UID_DialogTableReference = tp.UID_DialogTable204								where t.TableName = ''@ChildTable''205								 and c.ColumnName = ''@ChildColumn''206								) as y on @ChildTable.@ChildColumn like ''<Key><T>'' + y.TableName + ''</T>%'' 207	where y.TableName is null208	and @ChildTable.@ChildColumn > '' ''209%Mamig%		and @ChildTable.XObjectKey like ''<Key><T>@ChildTable</T><P>[a-z][0-z][0-z]-%'' 210%Mod%	and dbo.QBM_FGIModuleExists(%modcheck%) = 0211'212,213CASE214  WHEN @OwnerOfParent = '%' THEN215''216ELSE '217		and dbo.QBM_FCVObjectKeyToModuleOwner(@ChildTable.@ChildColumn) like ''@OwnerOfParent''218'219END,220'221end222'223)224DECLARE @cmdDeleteDynamicOutOfTables nvarchar(Max225) = CONCAT('226if exists (select top 1 1227			from @ChildTable  with (readpast)228%report%					join QBMForeignKeyWrongReport fkwr on @ChildTable.@ChildColumn = fkwr.InvalidValue229			where @ChildTable.@ChildColumn > '' ''230			 and not exists ( select top 1 1231								from #TablesForOutside a with (forceseek) 232								where left(@ChildTable.@ChildColumn,  patindex(''%</T>%'', @ChildTable.@ChildColumn ) + 3 ) =  a.KeyPattern 233							)234%Mamig%		and @ChildTable.XObjectKey like ''<Key><T>@ChildTable</T><P>[a-z][0-z][0-z]-%'' 235236			 '237,238CASE239  WHEN @OwnerOfParent = '%' THEN240''241ELSE '242			 and dbo.QBM_FCVObjectKeyToModuleOwner(@ChildTable.@ChildColumn) like ''@OwnerOfParent''243'244END,245'246		)247begin248%Xori%	update @ChildTable249%Xori%		set XOrigin = 0250%Xori%			from @ChildTable 251%Xori%%report%					join QBMForeignKeyWrongReport fkwr on @ChildTable.@ChildColumn = fkwr.InvalidValue252%Xori%			where @ChildTable.@ChildColumn > '' ''253%Xori%			 and not exists ( select top 1 1254%Xori%								from #TablesForOutside a with (forceseek) 255%Xori%								where left(@ChildTable.@ChildColumn,  patindex(''%</T>%'', @ChildTable.@ChildColumn ) + 3 ) =  a.KeyPattern 256%Xori%							)257258%Xori%%Mod%	and dbo.QBM_FGIModuleExists(%modcheck%) = 0259'260,261CASE262  WHEN @OwnerOfParent = '%' THEN263''264ELSE '265%Xori%		and dbo.QBM_FCVObjectKeyToModuleOwner(@ChildTable.@ChildColumn) like ''@OwnerOfParent''266'267END,268'269270	delete /* Platzhalter, keine Variable */ @ChildTable271			from @ChildTable 272%report%					join QBMForeignKeyWrongReport fkwr on @ChildTable.@ChildColumn = fkwr.InvalidValue273			where @ChildTable.@ChildColumn > '' ''274			 and not exists ( select top 1 1275								from #TablesForOutside a with (forceseek) 276								where left(@ChildTable.@ChildColumn,  patindex(''%</T>%'', @ChildTable.@ChildColumn ) + 3 ) =  a.KeyPattern 277							)278%Mamig%		and @ChildTable.XObjectKey like ''<Key><T>@ChildTable</T><P>[a-z][0-z][0-z]-%'' 279%Mod%	and dbo.QBM_FGIModuleExists(%modcheck%) = 0280'281,282CASE283  WHEN @OwnerOfParent = '%' THEN284''285ELSE '286		and dbo.QBM_FCVObjectKeyToModuleOwner(@ChildTable.@ChildColumn) like ''@OwnerOfParent''287'288END,289'290end291'292)293SELECT294  @cmdSetNull = CONCAT('295if exists (select top 1 1296			from @ChildTable with (readpast)297%report%					join QBMForeignKeyWrongReport fkwr on @ChildTable.@ChildColumn = fkwr.InvalidValue298			where not exists (select top 1 1299								from @ParentTable p   with (readpast)300								where p.@Parentcolumn = @ChildTable.@Childcolumn301							) 302			 and @ChildTable.@Childcolumn > '' ''303%Mamig%		and @ChildTable.XObjectKey like ''<Key><T>@ChildTable</T><P>[a-z][0-z][0-z]-%'' 304%dyn%		 and @ChildTable.@Childcolumn like ''<Key><T>@ParentTable</T>%'' 305'306  ,307CASE308  WHEN @OwnerOfParent = '%' THEN309''310ELSE '311%dyn%		 and dbo.QBM_FCVObjectKeyToModuleOwner(@ChildTable.@ChildColumn) like ''@OwnerOfParent''312%uid%		 and left(@ChildTable.@ChildColumn, 3) like ''@OwnerOfParent''313'314END,315'316		)317begin318	update @ChildTable319		set @Childcolumn = null 320		from @ChildTable321%report%					join QBMForeignKeyWrongReport fkwr on @ChildTable.@ChildColumn = fkwr.InvalidValue322		where not exists (select top 1 1323							from @ParentTable p   with (readpast)324							where p.@Parentcolumn = @ChildTable.@Childcolumn) 325	-- und der Childschlssel ist nicht leer326				 and @ChildTable.@Childcolumn > '' ''327%Mamig%		and @ChildTable.XObjectKey like ''<Key><T>@ChildTable</T><P>[a-z][0-z][0-z]-%'' 328%dyn%		 and @ChildTable.@Childcolumn like ''<Key><T>@ParentTable</T>%'' 329%Mod%	and dbo.QBM_FGIModuleExists(%modcheck%) = 0330'331,332CASE333  WHEN @OwnerOfParent = '%' THEN334''335ELSE '336%dyn%		 and dbo.QBM_FCVObjectKeyToModuleOwner(@ChildTable.@ChildColumn) like ''@OwnerOfParent''337%uid%		 and left(@ChildTable.@ChildColumn, 3) like ''@OwnerOfParent''338'339END,340'341end342'343)344DECLARE @cmdSetNullDynamicOutOfValid nvarchar(max345) = CONCAT('346if exists (select top 1 1347			from @ChildTable  with (readpast)348%report%					join QBMForeignKeyWrongReport fkwr on @ChildTable.@ChildColumn = fkwr.InvalidValue349						left outer join (350										select tp.TableName351										from DialogValidDynamicRef vr with (readpast) join DialogColumn c with (readpast) on vr.UID_DialogColumn = c.UID_DialogColumn352																	join DialogTable t with (readpast) on c.UID_DialogTable = t.UID_DialogTable353																	join DialogTable tp with (readpast) on vr.UID_DialogTableReference = tp.UID_DialogTable354										where t.TableName = ''@ChildTable''355										 and c.ColumnName = ''@ChildColumn''356										) as y on @ChildTable.@ChildColumn like ''<Key><T>'' + y.TableName + ''</T>%'' 357			where y.TableName is null358			and @ChildTable.@ChildColumn > '' ''359%Mamig%		and @ChildTable.XObjectKey like ''<Key><T>@ChildTable</T><P>[a-z][0-z][0-z]-%'' 360			'361,362CASE363  WHEN @OwnerOfParent = '%' THEN364''365ELSE '366			and dbo.QBM_FCVObjectKeyToModuleOwner(@ChildTable.@ChildColumn) like ''@OwnerOfParent''367'368END,369'370		)371begin372	update @ChildTable373		set @Childcolumn = null 374		from @ChildTable 375%report%					join QBMForeignKeyWrongReport fkwr on @ChildTable.@ChildColumn = fkwr.InvalidValue376					left outer join (377									select tp.TableName378									from DialogValidDynamicRef vr with (readpast) join DialogColumn c with (readpast) on vr.UID_DialogColumn = c.UID_DialogColumn379																join DialogTable t with (readpast) on c.UID_DialogTable = t.UID_DialogTable380																join DialogTable tp with (readpast) on vr.UID_DialogTableReference = tp.UID_DialogTable381									where t.TableName = ''@ChildTable''382									 and c.ColumnName = ''@ChildColumn''383									) as y on @ChildTable.@ChildColumn like ''<Key><T>'' + y.TableName + ''</T>%'' 384		where y.TableName is null385		and @ChildTable.@ChildColumn > '' ''386%Mamig%		and @ChildTable.XObjectKey like ''<Key><T>@ChildTable</T><P>[a-z][0-z][0-z]-%'' 387%Mod%	and dbo.QBM_FGIModuleExists(%modcheck%) = 0388'389,390CASE391  WHEN @OwnerOfParent = '%' THEN392''393ELSE '394		and dbo.QBM_FCVObjectKeyToModuleOwner(@ChildTable.@ChildColumn) like ''@OwnerOfParent''395'396END,397'398end399'400)401DECLARE @cmdSetNullDynamicOutOfTables nvarchar(max402) = CONCAT('403if exists (select top 1 1404			from @ChildTable  with (readpast)405%report%					join QBMForeignKeyWrongReport fkwr on @ChildTable.@ChildColumn = fkwr.InvalidValue406			where @ChildTable.@ChildColumn > '' ''407%Mamig%		and @ChildTable.XObjectKey like ''<Key><T>@ChildTable</T><P>[a-z][0-z][0-z]-%'' 408			 and not exists ( select top 1 1409								from #TablesForOutside a with (forceseek) 410								where left(@ChildTable.@ChildColumn,  patindex(''%</T>%'', @ChildTable.@ChildColumn ) + 3 ) =  a.KeyPattern 411							)412			 '413,414CASE415  WHEN @OwnerOfParent = '%' THEN416''417ELSE '418			 and dbo.QBM_FCVObjectKeyToModuleOwner(@ChildTable.@ChildColumn) like ''@OwnerOfParent''419'420END,421'422		)423begin424	update @ChildTable425		set @Childcolumn = null 426			from @ChildTable 427%report%					join QBMForeignKeyWrongReport fkwr on @ChildTable.@ChildColumn = fkwr.InvalidValue428			where @ChildTable.@ChildColumn > '' ''429%Mamig%		and @ChildTable.XObjectKey like ''<Key><T>@ChildTable</T><P>[a-z][0-z][0-z]-%'' 430			 and not exists ( select top 1 1431								from #TablesForOutside a with (forceseek) 432								where left(@ChildTable.@ChildColumn,  patindex(''%</T>%'', @ChildTable.@ChildColumn ) + 3 ) =  a.KeyPattern 433							)434%Mod%	and dbo.QBM_FGIModuleExists(%modcheck%) = 0435'436,437CASE438  WHEN @OwnerOfParent = '%' THEN439''440ELSE '441		and dbo.QBM_FCVObjectKeyToModuleOwner(@ChildTable.@ChildColumn) like ''@OwnerOfParent''442'443END,444'445end446'447)448DECLARE @cmdSetNullDynamicOutside nvarchar(max449) = CONCAT(N '450if exists (select top 1 1451			from @ChildTable x with (readpast)452%report%					join QBMForeignKeyWrongReport fkwr on @ChildTable.@ChildColumn = fkwr.InvalidValue453							left outer join @ParentBaseTable p on x.@ChildColumn = p.XObjectKey454				where x.@ChildColumn like ''<Key><T>@ParentTable</T><P>%''455				and p.XObjectKey is null					456				and x.@ChildColumn > '' ''457%Mamig%		and x.XObjectKey like ''<Key><T>@ChildTable</T><P>[a-z][0-z][0-z]-%'' 458				'459,460CASE461  WHEN @OwnerOfParent = '%' THEN462''463ELSE '464				and dbo.QBM_FCVObjectKeyToModuleOwner(x.@ChildColumn) like ''@OwnerOfParent''465'466END,467'468		)469begin470	update @ChildTable471		set @Childcolumn = null 472		from @ChildTable x473%report%					join QBMForeignKeyWrongReport fkwr on @ChildTable.@ChildColumn = fkwr.InvalidValue474							left outer join @ParentBaseTable p on x.@ChildColumn = p.XObjectKey475				where x.@ChildColumn like ''<Key><T>@ParentTable</T><P>%''476				and p.XObjectKey is null					477				and x.@ChildColumn > '' ''478%Mamig%		and x.XObjectKey like ''<Key><T>@ChildTable</T><P>[a-z][0-z][0-z]-%'' 479				'480,481CASE482  WHEN @OwnerOfParent = '%' THEN483''484ELSE '485				and dbo.QBM_FCVObjectKeyToModuleOwner(x.@ChildColumn) like ''@OwnerOfParent''486'487END,488'489%Mod%	and dbo.QBM_FGIModuleExists(%modcheck%) = 0490end491'492)493DECLARE @cmdDeleteDynamicOutside nvarchar(Max494) = CONCAT('495if exists (select top 1 1496			from @ChildTable x with (readpast)497%report%					join QBMForeignKeyWrongReport fkwr on @ChildTable.@ChildColumn = fkwr.InvalidValue498							left outer join @ParentBaseTable p on x.@ChildColumn = p.XObjectKey499				where x.@ChildColumn like ''<Key><T>@ParentTable</T><P>%''500				and p.XObjectKey is null					501				and x.@ChildColumn > '' ''502%Mamig%		and x.XObjectKey like ''<Key><T>@ChildTable</T><P>[a-z][0-z][0-z]-%'' 503				'504,505CASE506  WHEN @OwnerOfParent = '%' THEN507''508ELSE '509				and dbo.QBM_FCVObjectKeyToModuleOwner(x.@ChildColumn) like ''@OwnerOfParent''510'511END,512'513		)514begin515%Xori%	update @ChildTable516%Xori%		set XOrigin = 0517%Xori%		from @ChildTable x518%Xori%%report%					join QBMForeignKeyWrongReport fkwr on @ChildTable.@ChildColumn = fkwr.InvalidValue519%Xori%							left outer join @ParentBaseTable p on x.@ChildColumn = p.XObjectKey520%Xori%				where x.@ChildColumn like ''<Key><T>@ParentTable</T><P>%''521%Xori%				and p.XObjectKey is null					522%Xori%				and x.@ChildColumn > '' ''523%Mamig%		and x.XObjectKey like ''<Key><T>@ChildTable</T><P>[a-z][0-z][0-z]-%'' 524'525,526CASE527  WHEN @OwnerOfParent = '%' THEN528''529ELSE '530%Xori%				and dbo.QBM_FCVObjectKeyToModuleOwner(x.@ChildColumn) like ''@OwnerOfParent''531'532END,533'534%Xori%%Mod%	and dbo.QBM_FGIModuleExists(%modcheck%) = 0535536	delete /* Platzhalter, keine Variable */ @ChildTable537	from @ChildTable x538%report%					join QBMForeignKeyWrongReport fkwr on @ChildTable.@ChildColumn = fkwr.InvalidValue539							left outer join @ParentBaseTable p on x.@ChildColumn = p.XObjectKey540				where x.@ChildColumn like ''<Key><T>@ParentTable</T><P>%''541				and p.XObjectKey is null					542				and x.@ChildColumn > '' ''543%Mamig%		and x.XObjectKey like ''<Key><T>@ChildTable</T><P>[a-z][0-z][0-z]-%'' 544				'545,546CASE547  WHEN @OwnerOfParent = '%' THEN548''549ELSE '550				and dbo.QBM_FCVObjectKeyToModuleOwner(x.@ChildColumn) like ''@OwnerOfParent''551'552END,553'554%Mod%	and dbo.QBM_FGIModuleExists(%modcheck%) = 0555end556'557)558SELECT @changedTotal = 1559WHILE @changedTotal = 1560BEGIN561  SELECT @changedTotal = 0562  IF @DebugSwitch > 0563  BEGIN564    print N '##################### neuer Durchlauf #####################'565  END566  IF @RepairFromReport = 0567  BEGIN568    DELETE @ElementBufferMulti569    INSERT INTO @ElementBufferMulti(Ident1,570    Ident2,571    Ident3,572    LongIdent1,573    Bit1,574    UID1,575    UID3,576    UID2,577    Int2,578    Int3579  )580  SELECT581    fr.Parenttable,582    fr.ChildTable,583    fr.ParentColumn,584    fr.ChildColumn,585    fr.IsChildPKMember,586    fr.ParentRestriction,587    fr.IsChildNullAble,588    fr.RepairMethod,589    fr.IsDynamicFK,590    sign(len(ISNULL(c.uid_dialogColumn,591    ''))592    )593    AS594    HasXObjectKey595FROM QBM_VForeignKeyRepairable fr596JOIN DialogTable t597  WITH(readpast)598  ON fr.Childtable = t.TableName599LEFT600OUTER601JOIN DialogColumn c602  WITH(readpast)603  ON c.UID_DialogTable = t.UID_DialogTable AND c.ColumnName = 'XObjectKey'604WHERE605  fr.ChildTable LIKE @ChildTablePattern AND fr.ChildColumn LIKE @ChildColumnPattern AND(fr.ParentTable LIKE @ParentTablePattern OR fr.Parenttable606= '%') AND fr.ParentColumn LIKE @ParentColumnPattern AND(@FilterByModuleGuidAllowed = 0 OR @FilterByModuleGuidAllowed = 1 AND fr.IsParentTabModuleGUIDAllowed607= 1) AND(@MasterMigMode = 0 OR @MasterMigMode = 1 AND EXISTS(608SELECT TOP 1 1609FROM DialogColumn cx610WHERE611  cx.UID_DialogTable = t.UID_DialogTable AND c.ColumnName = 'XObjectkey'))612ORDER BY fr.ChildTable,613fr.ChildColumn,614fr.ParentTable,615fr.ParentColumn616SELECT @ElementCount = @@ROWCOUNT617SELECT @ElementIndex = @@IDENTITY - @ElementCount +1618SELECT @ElementLast = @@IDENTITY619IF @CheckOutsideValues = 1620BEGIN621  INSERT INTO @ElementBufferOutsideValues(ContentFull)622  SELECT623    '624			select distinct t.tablename as tabletarget, ''' + yt.TableName + ''', ''' + yc.ColumnName + '''625				from ' + yt.TableName + ' x join DialogTable t on x.'626    + yc.ColumnName + ' like ''<Key><T>'' + t.TableName + ''</T><P>%''627				'628  FROM DialogColumn yc629    WITH(readpast)630  JOIN DialogTable yt631    WITH(readpast)632    ON yc.UID_DialogTable = yt.UID_DialogTable AND yt.TableType IN('V',633  'T') AND yt.UsageType NOT IN('HISTORY')634  LEFT635  OUTER636  JOIN DialogValidDynamicRef r637    WITH(readpast)638    ON yc.UID_DialogColumn = r.UID_DialogColumn639  WHERE640    yc.IsDynamicFK = 1 AND r.UID_DialogColumn IS NULL AND yc.SchemaDataLen = 138 AND yc.SchemaDataType = 'varchar' AND yc.ColumnName <> 'XObjectKey' AND641  NOT EXISTS(642  SELECT TOP 1 1643  FROM(644  VALUES('QER-4E75B64F5997406CAC342BAB6A46F8BE'),('QER-27E0BCAF583B480499300CFF790725E7'),('QER-9C38234735B84413BE4C612A1A7F7324'),645  ('QER-36A10DEFD23E469C8132EFB58032DD85'),('HDB-B6167ABE67A74F8DB42E2CBD2D50CE56'),('HDB-3935E716EDB64CB587D2ECA6F93ABEA9')) AS ex(UID_DialogColumn646  )647  WHERE648    ex.UID_DialogColumn = yc.UID_DialogColumn) AND NOT(yc.UID_DialogColumn = 'QBM-FAC53888D84E44CFA01B0C043CD53DE7' AND dbo.QBM_FGIDBOwner() =649  'CCC')650  SELECT @ElementCountOutsideValues = @@ROWCOUNT651  SELECT @ElementIndexOutsideValues = @@IDENTITY - @ElementCountOutsideValues +1652  SELECT @ElementLastOutsideValues = @@IDENTITY653  WHILE @ElementIndexOutsideValues <= @ElementLastOutsideValues654  BEGIN655    SELECT TOP 1 @SQLOutsideValues = bu.ContentFull656    FROM @ElementBufferOutsideValues bu657    WHERE658      bu.ElementIndex = @ElementIndexOutsideValues659    INSERT INTO @WerOutsideValues(Parameter1,660    Parameter2,661    Parameter3)662    EXEC sp_executesql @SQLOutsideValues663    SELECT @ElementIndexOutsideValues += 1664  END665  INSERT INTO @ElementBufferMulti(Ident1,666  Ident2,667  Ident3,668  LongIdent1,669  Bit1,670  UID1,671  UID3,672  UID2,673  LongIdent2,674  Int2)675  SELECT676    tp.TableName AS ParentTable,677    t.TableName AS ChildTable,678    'XObjectKey' AS ParentColumn,679    c.ColumnName AS ChildColumn,680    c.IsPKMember AS IsChildPKMember,681    'D' AS ParentRestriction,682    cc.IS_NULLABLE AS IsChildNullAble,683  CASE684    WHEN c.IsPKMember = 1 OR cc.IS_NULLABLE = 'NO' THEN685  'Delete'686  ELSE 'SetNull'687  END AS RepairMethod,688  'OutsideAllExistingValues' AS RelationID,689  4 AS IsDynamicFK690  FROM DialogColumn c691    WITH(readpast)692  JOIN DialogTable t693    WITH(readpast)694    ON c.UID_DialogTable = t.UID_DialogTable695  JOIN @WerOutsideValues w696    ON w.Parameter2 = t.TableName AND w.Parameter3 = c.ColumnName697  JOIN DialogTable tp698    WITH(readpast)699    ON tp.TableName = w.Parameter1700  JOIN INFORMATION_SCHEMA.COLUMNS cc701    WITH(readpast)702    ON cc.TABLE_NAME = t.TableName AND cc.COLUMN_NAME = c.ColumnName703  WHERE704    t.TableName LIKE @ChildTablePattern AND c.ColumnName LIKE @ChildColumnPattern AND(tp.TableName LIKE @ParentTablePattern OR tp.TableName705  = '%') AND 'XObjectKey' LIKE @ParentColumnPattern AND(@FilterByModuleGuidAllowed = 0 OR @FilterByModuleGuidAllowed = 1 AND tp.IsModuleGUIDAllowed706  = 1) AND NOT(c.UID_DialogColumn = 'QBM-FAC53888D84E44CFA01B0C043CD53DE7' AND dbo.QBM_FGIDBOwner() = 'CCC') AND(@MasterMigMode = 0 OR @MasterMigMode707  = 1 AND EXISTS(708  SELECT TOP 1 1709  FROM DialogColumn cx710  WHERE711    cx.UID_DialogTable = t.UID_DialogTable AND c.ColumnName = 'XObjectkey'))712  SELECT @ElementCount += @@ROWCOUNT713  SELECT @ElementIndex = @@IDENTITY - @ElementCount +1714  SELECT @ElementLast = @@IDENTITY715END716END717ELSE718BEGIN719  DELETE @ElementBufferMulti720  INSERT INTO @ElementBufferMulti(Ident1,721  Ident2,722  Ident3,723  LongIdent1,724  Bit1,725  UID1,726  UID3,727  UID2,728  Int2,729  Int3)730  SELECT731    fr.Parenttable,732    fr.ChildTable,733    fr.ParentColumn,734    fr.ChildColumn,735    fr.IsChildPKMember,736    fr.ParentRestriction,737    fr.IsChildNullAble,738    fr.RepairMethod,739    vr.IsDynamicFK AS IsDynamicFK,740    sign(len(ISNULL(c.uid_dialogColumn, ''))) AS HasXObjectKey741  FROM QBMForeignKeyWrongReport fr742  JOIN QBM_VForeignKeyRepairable vr743    ON fr.ParentTable = vr.Parenttable AND fr.ParentColumn = vr.ParentColumn AND fr.ChildTable = vr.Childtable AND fr.ChildColumn = vr.ChildColumn744  JOIN sys.tables tp745    WITH(readpast)746    ON fr.ParentTable = tp.name747  JOIN sys.columns cp748    WITH(readpast)749    ON tp.object_id = cp.object_id AND cp.name = fr.ParentColumn750  JOIN DialogTable t751    WITH(readpast)752    ON fr.Childtable = t.TableName753  JOIN DialogColumn cc754    WITH(readpast)755    ON t.UID_DialogTable = cc.UID_DialogTable AND cc.ColumnName = fr.ChildColumn756  LEFT757  OUTER758  JOIN DialogColumn c759    WITH(readpast)760    ON c.UID_DialogTable = t.UID_DialogTable AND c.ColumnName = 'XObjectKey'761  WHERE762    fr.Parenttable > ' ' AND(@MasterMigMode = 0 OR @MasterMigMode = 1 AND EXISTS(763  SELECT TOP 1 1764  FROM DialogColumn cx765  WHERE766    cx.UID_DialogTable = t.UID_DialogTable AND c.ColumnName = 'XObjectkey'))767  UNION768  SELECT769    '%',770    fr.ChildTable,771    fr.ParentColumn,772    fr.ChildColumn,773    fr.IsChildPKMember,774    fr.ParentRestriction,775    fr.IsChildNullAble,776    fr.RepairMethod,777  CASE fr.RelationID778    WHEN 'OutsideAllTables' THEN779  3780    WHEN 'OutsideAllExistingValues' THEN781  2782  ELSE 0783  END AS IsDynamicFK,784  sign(len(ISNULL(c.uid_dialogColumn, ''))) AS HasXObjectKey785  FROM QBMForeignKeyWrongReport fr786  JOIN DialogTable t787    WITH(readpast)788    ON fr.Childtable = t.TableName789  JOIN DialogColumn cc790    WITH(readpast)791    ON t.UID_DialogTable = cc.UID_DialogTable AND cc.ColumnName = fr.ChildColumn792  LEFT793  OUTER794  JOIN DialogColumn c795    WITH(readpast)796    ON c.UID_DialogTable = t.UID_DialogTable AND c.ColumnName = 'XObjectKey'797  WHERE798    fr.RelationID IN('OutsideAllTables',799  'OutsideAllExistingValues') AND(@MasterMigMode = 0 OR @MasterMigMode = 1 AND EXISTS(800  SELECT TOP 1 1801  FROM DialogColumn cx802  WHERE803    cx.UID_DialogTable = t.UID_DialogTable AND c.ColumnName = 'XObjectkey'))804  ORDER BY fr.ChildTable,805  fr.ChildColumn,806  fr.ParentTable,807  fr.ParentColumn808  SELECT @ElementCount = @@ROWCOUNT809  SELECT @ElementIndex = @@IDENTITY - @ElementCount +1810  SELECT @ElementLast = @@IDENTITY811END812WHILE @ElementIndex <= @ElementLast813BEGIN814  SELECT815    TOP 1 @ParentTable = bu.Ident1,816    @ChildTable = bu.Ident2,817    @ParentColumn = bu.Ident3,818    @ChildColumn = bu.LongIdent1,819    @isPKMember = bu.Bit1,820    @ParentRestriction = bu.UID1,821    @isNullableColumn = bu.UID3,822    @RepairMethod = bu.UID2,823    @IsDynamicFK = bu.Int2,824    @HasXObjectKey = bu.Int3825  FROM @ElementBufferMulti bu826  WHERE827    bu.ElementIndex = @ElementIndex828  SELECT829    @ParentTableBase = isnull(b.TableName,830    @ParentTable)831  FROM DialogTable v832  LEFT833  OUTER834  JOIN DialogTable b835    ON v.UID_DialogTableBase = b.UID_DialogTable836  WHERE837    v.TableName = @ParentTable838  IF @ParentTableBase IS NULL839  BEGIN840    SELECT @ParentTableBase = @ParentTable841  END842  IF @RepairMethod = 'Delete'843  BEGIN844    SELECT @cmdwork = CASE @IsDynamicFK845    WHEN 2 THEN846    @cmdDeleteDynamicOutOfValid847    WHEN 3 THEN848    @cmdDeleteDynamicOutOfTables849    WHEN 4 THEN850    @cmdDeleteDynamicOutside851    ELSE @cmdDelete852    END853  END854  ELSE855  BEGIN856    SELECT @cmdwork = CASE @IsDynamicFK857    WHEN 2 THEN858    @cmdSetNullDynamicOutOfValid859    WHEN 3 THEN860    @cmdSetNullDynamicOutOfTables861    WHEN 4 THEN862    @cmdSetNullDynamicOutside863    ELSE @cmdSetNull864    END865  END866  IF @IsDynamicFK > 0867  BEGIN868    SELECT869      @cmdwork = replace(@cmdwork,870      '%modcheck%',871      @ModCheck138)872  END873  ELSE874  BEGIN875    IF @HasXObjectKey = 1876    BEGIN877      SELECT878        @cmdwork = replace(@cmdwork,879        '%modcheck%',880        @ModCheck38X)881    END882    ELSE883    BEGIN884      SELECT885        @cmdwork = replace(@cmdwork,886        '%modcheck%',887        @ModCheck38K)888    END889  END890  SELECT891    @cmdwork = replace(@cmdWork,892    N '@ChildTable',893    @ChildTable)894  SELECT895    @cmdwork = replace(@cmdwork,896    N '@ParentTable',897    @ParentTable)898  SELECT899    @cmdwork = replace(@cmdwork,900    N '@Childcolumn',901    @Childcolumn)902  SELECT903    @cmdwork = replace(@cmdwork,904    N '@Parentcolumn',905    @Parentcolumn)906  SELECT907    @cmdwork = replace(@cmdwork,908    N '%dyn%',909  CASE @IsDynamicFK910    WHEN 1 THEN911  ''912  ELSE '--'913  END)914  SELECT915    @cmdwork = replace(@cmdwork,916    N '%uid%',917  CASE @IsDynamicFK918    WHEN 1 THEN919  '--'920  ELSE ''921  END)922  SELECT923    @cmdwork = replace(@cmdwork,924    N '@OwnerOfParent',925    @OwnerOfParent)926  IF EXISTS(927    SELECT TOP 1 1928    FROM DialogTable t929    WITH(readpast)930  JOIN DialogColumn c931    WITH(readpast)932    ON t.UID_DialogTable = c.UID_DialogTable933  WHERE934    t.TableName = @ChildTable AND c.ColumnName = 'XOrigin')935  BEGIN936    SELECT937      @cmdwork = replace(@cmdwork,938      '%Xori%',939      '')940  END941  ELSE942  BEGIN943    SELECT944      @cmdwork = replace(@cmdwork,945      '%Xori%',946      '--')947  END948  IF @RepairOnlyNonExistingModule = 1949  BEGIN950    SELECT951      @cmdwork = replace(@cmdwork,952      '%Mod%',953      '')954  END955  ELSE956  BEGIN957    SELECT958      @cmdwork = replace(@cmdwork,959      '%Mod%',960      '--')961  END962  IF @RepairFromReport = 1963  BEGIN964    SELECT965      @cmdwork = replace(@cmdwork,966      '%report%',967      '')968  END969  ELSE970  BEGIN971    SELECT972      @cmdwork = replace(@cmdwork,973      '%report%',974      '--')975  END976  SELECT977    @cmdwork = replace(@cmdwork,978    '@ParentBaseTable',979    @ParentTableBase)980  IF @MasterMigMode = 1981  BEGIN982    SELECT983      @cmdwork = replace(@cmdwork,984      '%Mamig%',985      '')986  END987  ELSE988  BEGIN989    SELECT990      @cmdwork = replace(@cmdwork,991      '%Mamig%',992      '--')993  END994  IF @DebugSwitch > 0995  BEGIN996    print isnull(@cmdwork,997    'hich')998  END999  SELECT1000    @msg = @ParentTable + '.' + @ParentColumn + ' -> ' + @ChildTable + '.' + @ChildColumn1001  SELECT @changed = 11002  WHILE @changed = 11003  BEGIN1004    IF @DebugSwitch = 21005    BEGIN1006      print @cmdwork1007    END1008    EXEC @CountItems = QBM_PExecuteSQLWithRetry_LLP @SQLStatement = @cmdwork,1009      @LockTimeout_ms = DEFAULT,1010      @MaxWaitTimeForLock_s = DEFAULT,1011      @ProcIDForJournal = @@procid,1012      @HandleErrorSilent = 01013    IF @CountItems > 01014    BEGIN1015      IF @ParentTable = @ChildTable1016      BEGIN1017        SELECT @changed = 11018      END1019      ELSE1020      BEGIN1021        SELECT @ChangesForeign = 11022        SELECT @changed = 01023      END1024      SELECT @changedTotal = 11025      IF @DebugSwitch > 01026      BEGIN1027        print 'Change ' + @msg1028      END1029    END1030    ELSE1031    BEGIN1032      SELECT @changed = 01033      IF @DebugSwitch > 01034      BEGIN1035        print 'OK     ' + @msg1036      END1037    END1038  END1039  SELECT @ElementIndex += 11040END1041END1042END TRY1043BEGIN CATCH1044  EXEC QBM_PSessionErrorAdd DEFAULT1045  DECLARE @Rethrow varchar(1000) = dbo.QBM_FGISessionErrorRethrow()1046  RAISERROR(@Rethrow,1047  18,1048  1)1049    WITH NOWAIT1050END CATCH1051ende: truncate TABLE #TablesForOutside1052RETURN(@ChangesForeign)1053END
Open raw exported source
SQL · Raw449 lines
1   create   procedure QBM_PForeignKeyWrongRepair (@ChildTablePattern varchar(32) = '%' , @ChildColumnPattern varchar(32) = '%' , @ParentTablePattern2 varchar(32) = '%' , @ParentColumnPattern varchar(32) = '%' , @RepairOnlyNonExistingModule bit = 0  , @OwnerOfParent varchar(3) = '%'  , @RepairFromReport3 bit = 0   , @FilterByModuleGuidAllowed bit = 0    , @CheckOutsideValues bit = 0   , @MasterMigMode bit = 0  ) as begin declare @ParentTable nvarchar(644), @ChildTable nvarchar(64), @ParentColumn nvarchar(64), @ChildColumn nvarchar(64), @RepairMethod nvarchar(16) declare @ParentTableBase varchar(64) declare5 @ParentRestriction nvarchar(16) declare @isNullableColumn nvarchar(10) declare @isPKMember bit declare @changed bit declare @changedTotal bit declare 6@CountItems int declare @CmdWork nvarchar(max) declare @CmdDelete nvarchar(max) declare @CmdSetNull nvarchar(max) declare @msg nvarchar(1000) declare @IsDynamicFK7 int declare @HasXObjectKey bit declare @ChangesForeign int = 0  declare @DebugSwitch int = 0 declare @ElementCount int declare @ElementIndex int declare8 @ElementLast int declare @ElementBufferMulti QBM_YCursorBuffer declare @ElementCountOutsideValues int declare @ElementIndexOutsideValues int declare @ElementLastOutsideValues9 int declare @ElementBufferOutsideValues QBM_YCursorBuffer declare @SQLOutsideValues nvarchar(max) declare @WerOutsideValues QBM_YParameterList SET XACT_ABORT10 OFF BEGIN TRY set nocount on  drop table if exists #TablesForOutside  CREATE TABLE #TablesForOutside ( KeyPattern varchar(138) collate database_default11 not null primary key ) insert into #TablesForOutside(KeyPattern ) select '<Key><T>' + t.TableName + '</T>' from DialogTable t  if @RepairFromReport = 120  begin exec QBM_PForeignKeyEmptySetNull @ChildTablePattern end declare @ModCheck38X nvarchar(max) = ' dbo.QBM_FCVObjectKeyToModuleOwner(@ChildTable.XObjectKey) '13 declare @ModCheck38K nvarchar(max) = ' case dbo.QBM_FCVGUIDToModuleOwner(@ChildTable.@Childcolumn)  when ''ZZZ'' then left(@ChildTable.@Childcolumn, 3) else dbo.QBM_FCVGUIDToModuleOwner(@ChildTable.@Childcolumn) end '14 declare @ModCheck138 nvarchar(max) = ' dbo.QBM_FCVObjectKeyToModuleOwner(@ChildTable.@Childcolumn) ' select @cmdDelete = concat('15if exists (select top 1 116			from @ChildTable with (readpast)17%report%					join QBMForeignKeyWrongReport fkwr on @ChildTable.@ChildColumn = fkwr.InvalidValue18			where not exists (select top 1 119								 from @ParentTable p  with (readpast)20								where p.@Parentcolumn = @ChildTable.@Childcolumn21							) 22			 and @ChildTable.@Childcolumn > '' ''23%Mamig%		 and @ChildTable.XObjectKey like ''<Key><T>@ChildTable</T><P>[a-z][0-z][0-z]-%'' 24%dyn%		 and @ChildTable.@Childcolumn like ''<Key><T>@ParentTable</T>%'' 25'26, case when @OwnerOfParent = '%' then '' else '27%dyn%		 and dbo.QBM_FCVObjectKeyToModuleOwner(@ChildTable.@ChildColumn) like ''@OwnerOfParent''28%uid%		 and left(@ChildTable.@ChildColumn, 3) like ''@OwnerOfParent''29'30 end, '31		)32begin33%Xori%	update @ChildTable 34%Xori%		set XOrigin = 035%Xori%	  from @ChildTable 36%Xori%%report%					join QBMForeignKeyWrongReport fkwr on @ChildTable.@ChildColumn = fkwr.InvalidValue37%Xori%		where not exists (select top 1 138%Xori%							 from @ParentTable p  with (readpast)39%Xori%							where p.@Parentcolumn = @ChildTable.@Childcolumn40%Xori%						) 41%Xori%	-- und der Childschlssel ist nicht leer42%Xori%		 and @ChildTable.@Childcolumn > '' ''43%Xori%%dyn%		 and @ChildTable.@Childcolumn like ''<Key><T>@ParentTable</T>%'' 44%Xori%%Mod%	and dbo.QBM_FGIModuleExists(%modcheck%) = 045'46, case when @OwnerOfParent = '%' then '' else '47%Xori%%dyn%		 and dbo.QBM_FCVObjectKeyToModuleOwner(@ChildTable.@ChildColumn) like ''@OwnerOfParent''48%Xori%%uid%		 and left(@ChildTable.@ChildColumn, 3) like ''@OwnerOfParent''49'50 end, '5152	delete /* Platzhalter, keine Variable */ @ChildTable53		from @ChildTable54%report%					join QBMForeignKeyWrongReport fkwr on @ChildTable.@ChildColumn = fkwr.InvalidValue55		where not exists (select top 1 156							 from @ParentTable p   with (readpast)57							where p.@Parentcolumn = @ChildTable.@Childcolumn58						) 59	-- und der Childschlssel ist nicht leer60		 and @ChildTable.@Childcolumn > '' ''61%dyn%		 and @ChildTable.@Childcolumn like ''<Key><T>@ParentTable</T>%'' 62'63, case when @OwnerOfParent = '%' then '' else '64%dyn%		 and dbo.QBM_FCVObjectKeyToModuleOwner(@ChildTable.@ChildColumn) like ''@OwnerOfParent''65%uid%		 and left(@ChildTable.@ChildColumn, 3) like ''@OwnerOfParent''66'67 end, '6869%Mod%	and dbo.QBM_FGIModuleExists(%modcheck%) = 070%Mamig%		and @ChildTable.XObjectKey like ''<Key><T>@ChildTable</T><P>[a-z][0-z][0-z]-%'' 71end72'73 ) declare @cmdDeleteDynamicOutOfValid nvarchar(Max) = concat('74if exists (select top 1 175			from @ChildTable  with (readpast)76%report%					join QBMForeignKeyWrongReport fkwr on @ChildTable.@ChildColumn = fkwr.InvalidValue77						left outer join (78										select tp.TableName79										from DialogValidDynamicRef vr with (readpast) join DialogColumn c with (readpast) on vr.UID_DialogColumn = c.UID_DialogColumn80																	join DialogTable t with (readpast) on c.UID_DialogTable = t.UID_DialogTable81																	join DialogTable tp with (readpast) on vr.UID_DialogTableReference = tp.UID_DialogTable82										where t.TableName = ''@ChildTable''83										 and c.ColumnName = ''@ChildColumn''84										) as y on @ChildTable.@ChildColumn like ''<Key><T>'' + y.TableName + ''</T>%'' 85			where y.TableName is null86%Mamig%		and @ChildTable.XObjectKey like ''<Key><T>@ChildTable</T><P>[a-z][0-z][0-z]-%'' 87			'88, case when @OwnerOfParent = '%' then '' else '89			and dbo.QBM_FCVObjectKeyToModuleOwner(@ChildTable.@ChildColumn) like ''@OwnerOfParent''90' end, '91		)92begin93%Xori%	update @ChildTable94%Xori%		set XOrigin = 095%Xori%		from @ChildTable 96%Xori%%report%					join QBMForeignKeyWrongReport fkwr on @ChildTable.@ChildColumn = fkwr.InvalidValue97%Xori%					left outer join (98%Xori%									select tp.TableName99%Xori%									from DialogValidDynamicRef vr with (readpast) join DialogColumn c with (readpast) on vr.UID_DialogColumn = c.UID_DialogColumn100%Xori%																join DialogTable t with (readpast) on c.UID_DialogTable = t.UID_DialogTable101%Xori%																join DialogTable tp with (readpast) on vr.UID_DialogTableReference = tp.UID_DialogTable102%Xori%									where t.TableName = ''@ChildTable''103%Xori%									 and c.ColumnName = ''@ChildColumn''104%Xori%									) as y on @ChildTable.@ChildColumn like ''<Key><T>'' + y.TableName + ''</T>%'' 105%Xori%		where y.TableName is null106%Xori%		and @ChildTable.@ChildColumn > '' ''107%Xori%%Mod%	and dbo.QBM_FGIModuleExists(%modcheck%) = 0108'109, case when @OwnerOfParent = '%' then '' else '110%Xori%		and dbo.QBM_FCVObjectKeyToModuleOwner(@ChildTable.@ChildColumn) like ''@OwnerOfParent''111' end112, '113114	delete /* Platzhalter, keine Variable */ @ChildTable115	from @ChildTable 116%report%					join QBMForeignKeyWrongReport fkwr on @ChildTable.@ChildColumn = fkwr.InvalidValue117				left outer join (118								select tp.TableName119								from DialogValidDynamicRef vr with (readpast) join DialogColumn c with (readpast) on vr.UID_DialogColumn = c.UID_DialogColumn120															join DialogTable t with (readpast) on c.UID_DialogTable = t.UID_DialogTable121															join DialogTable tp with (readpast) on vr.UID_DialogTableReference = tp.UID_DialogTable122								where t.TableName = ''@ChildTable''123								 and c.ColumnName = ''@ChildColumn''124								) as y on @ChildTable.@ChildColumn like ''<Key><T>'' + y.TableName + ''</T>%'' 125	where y.TableName is null126	and @ChildTable.@ChildColumn > '' ''127%Mamig%		and @ChildTable.XObjectKey like ''<Key><T>@ChildTable</T><P>[a-z][0-z][0-z]-%'' 128%Mod%	and dbo.QBM_FGIModuleExists(%modcheck%) = 0129'130, case when @OwnerOfParent = '%' then '' else '131		and dbo.QBM_FCVObjectKeyToModuleOwner(@ChildTable.@ChildColumn) like ''@OwnerOfParent''132' end, '133end134'135 ) declare @cmdDeleteDynamicOutOfTables nvarchar(Max) = concat('136if exists (select top 1 1137			from @ChildTable  with (readpast)138%report%					join QBMForeignKeyWrongReport fkwr on @ChildTable.@ChildColumn = fkwr.InvalidValue139			where @ChildTable.@ChildColumn > '' ''140			 and not exists ( select top 1 1141								from #TablesForOutside a with (forceseek) 142								where left(@ChildTable.@ChildColumn,  patindex(''%</T>%'', @ChildTable.@ChildColumn ) + 3 ) =  a.KeyPattern 143							)144%Mamig%		and @ChildTable.XObjectKey like ''<Key><T>@ChildTable</T><P>[a-z][0-z][0-z]-%'' 145146			 '147, case when @OwnerOfParent = '%' then '' else '148			 and dbo.QBM_FCVObjectKeyToModuleOwner(@ChildTable.@ChildColumn) like ''@OwnerOfParent''149' end, '150		)151begin152%Xori%	update @ChildTable153%Xori%		set XOrigin = 0154%Xori%			from @ChildTable 155%Xori%%report%					join QBMForeignKeyWrongReport fkwr on @ChildTable.@ChildColumn = fkwr.InvalidValue156%Xori%			where @ChildTable.@ChildColumn > '' ''157%Xori%			 and not exists ( select top 1 1158%Xori%								from #TablesForOutside a with (forceseek) 159%Xori%								where left(@ChildTable.@ChildColumn,  patindex(''%</T>%'', @ChildTable.@ChildColumn ) + 3 ) =  a.KeyPattern 160%Xori%							)161162%Xori%%Mod%	and dbo.QBM_FGIModuleExists(%modcheck%) = 0163'164, case when @OwnerOfParent = '%' then '' else '165%Xori%		and dbo.QBM_FCVObjectKeyToModuleOwner(@ChildTable.@ChildColumn) like ''@OwnerOfParent''166' end167, '168169	delete /* Platzhalter, keine Variable */ @ChildTable170			from @ChildTable 171%report%					join QBMForeignKeyWrongReport fkwr on @ChildTable.@ChildColumn = fkwr.InvalidValue172			where @ChildTable.@ChildColumn > '' ''173			 and not exists ( select top 1 1174								from #TablesForOutside a with (forceseek) 175								where left(@ChildTable.@ChildColumn,  patindex(''%</T>%'', @ChildTable.@ChildColumn ) + 3 ) =  a.KeyPattern 176							)177%Mamig%		and @ChildTable.XObjectKey like ''<Key><T>@ChildTable</T><P>[a-z][0-z][0-z]-%'' 178%Mod%	and dbo.QBM_FGIModuleExists(%modcheck%) = 0179'180, case when @OwnerOfParent = '%' then '' else '181		and dbo.QBM_FCVObjectKeyToModuleOwner(@ChildTable.@ChildColumn) like ''@OwnerOfParent''182' end, '183end184'185 ) select @cmdSetNull = concat('186if exists (select top 1 1187			from @ChildTable with (readpast)188%report%					join QBMForeignKeyWrongReport fkwr on @ChildTable.@ChildColumn = fkwr.InvalidValue189			where not exists (select top 1 1190								from @ParentTable p   with (readpast)191								where p.@Parentcolumn = @ChildTable.@Childcolumn192							) 193			 and @ChildTable.@Childcolumn > '' ''194%Mamig%		and @ChildTable.XObjectKey like ''<Key><T>@ChildTable</T><P>[a-z][0-z][0-z]-%'' 195%dyn%		 and @ChildTable.@Childcolumn like ''<Key><T>@ParentTable</T>%'' 196'197, case when @OwnerOfParent = '%' then '' else '198%dyn%		 and dbo.QBM_FCVObjectKeyToModuleOwner(@ChildTable.@ChildColumn) like ''@OwnerOfParent''199%uid%		 and left(@ChildTable.@ChildColumn, 3) like ''@OwnerOfParent''200'201 end, '202		)203begin204	update @ChildTable205		set @Childcolumn = null 206		from @ChildTable207%report%					join QBMForeignKeyWrongReport fkwr on @ChildTable.@ChildColumn = fkwr.InvalidValue208		where not exists (select top 1 1209							from @ParentTable p   with (readpast)210							where p.@Parentcolumn = @ChildTable.@Childcolumn) 211	-- und der Childschlssel ist nicht leer212				 and @ChildTable.@Childcolumn > '' ''213%Mamig%		and @ChildTable.XObjectKey like ''<Key><T>@ChildTable</T><P>[a-z][0-z][0-z]-%'' 214%dyn%		 and @ChildTable.@Childcolumn like ''<Key><T>@ParentTable</T>%'' 215%Mod%	and dbo.QBM_FGIModuleExists(%modcheck%) = 0216'217, case when @OwnerOfParent = '%' then '' else '218%dyn%		 and dbo.QBM_FCVObjectKeyToModuleOwner(@ChildTable.@ChildColumn) like ''@OwnerOfParent''219%uid%		 and left(@ChildTable.@ChildColumn, 3) like ''@OwnerOfParent''220'221 end, '222end223') declare @cmdSetNullDynamicOutOfValid nvarchar(max) = concat('224if exists (select top 1 1225			from @ChildTable  with (readpast)226%report%					join QBMForeignKeyWrongReport fkwr on @ChildTable.@ChildColumn = fkwr.InvalidValue227						left outer join (228										select tp.TableName229										from DialogValidDynamicRef vr with (readpast) join DialogColumn c with (readpast) on vr.UID_DialogColumn = c.UID_DialogColumn230																	join DialogTable t with (readpast) on c.UID_DialogTable = t.UID_DialogTable231																	join DialogTable tp with (readpast) on vr.UID_DialogTableReference = tp.UID_DialogTable232										where t.TableName = ''@ChildTable''233										 and c.ColumnName = ''@ChildColumn''234										) as y on @ChildTable.@ChildColumn like ''<Key><T>'' + y.TableName + ''</T>%'' 235			where y.TableName is null236			and @ChildTable.@ChildColumn > '' ''237%Mamig%		and @ChildTable.XObjectKey like ''<Key><T>@ChildTable</T><P>[a-z][0-z][0-z]-%'' 238			'239, case when @OwnerOfParent = '%' then '' else '240			and dbo.QBM_FCVObjectKeyToModuleOwner(@ChildTable.@ChildColumn) like ''@OwnerOfParent''241' end, '242		)243begin244	update @ChildTable245		set @Childcolumn = null 246		from @ChildTable 247%report%					join QBMForeignKeyWrongReport fkwr on @ChildTable.@ChildColumn = fkwr.InvalidValue248					left outer join (249									select tp.TableName250									from DialogValidDynamicRef vr with (readpast) join DialogColumn c with (readpast) on vr.UID_DialogColumn = c.UID_DialogColumn251																join DialogTable t with (readpast) on c.UID_DialogTable = t.UID_DialogTable252																join DialogTable tp with (readpast) on vr.UID_DialogTableReference = tp.UID_DialogTable253									where t.TableName = ''@ChildTable''254									 and c.ColumnName = ''@ChildColumn''255									) as y on @ChildTable.@ChildColumn like ''<Key><T>'' + y.TableName + ''</T>%'' 256		where y.TableName is null257		and @ChildTable.@ChildColumn > '' ''258%Mamig%		and @ChildTable.XObjectKey like ''<Key><T>@ChildTable</T><P>[a-z][0-z][0-z]-%'' 259%Mod%	and dbo.QBM_FGIModuleExists(%modcheck%) = 0260'261, case when @OwnerOfParent = '%' then '' else '262		and dbo.QBM_FCVObjectKeyToModuleOwner(@ChildTable.@ChildColumn) like ''@OwnerOfParent''263' end, '264end265'266) declare @cmdSetNullDynamicOutOfTables nvarchar(max) = concat('267if exists (select top 1 1268			from @ChildTable  with (readpast)269%report%					join QBMForeignKeyWrongReport fkwr on @ChildTable.@ChildColumn = fkwr.InvalidValue270			where @ChildTable.@ChildColumn > '' ''271%Mamig%		and @ChildTable.XObjectKey like ''<Key><T>@ChildTable</T><P>[a-z][0-z][0-z]-%'' 272			 and not exists ( select top 1 1273								from #TablesForOutside a with (forceseek) 274								where left(@ChildTable.@ChildColumn,  patindex(''%</T>%'', @ChildTable.@ChildColumn ) + 3 ) =  a.KeyPattern 275							)276			 '277, case when @OwnerOfParent = '%' then '' else '278			 and dbo.QBM_FCVObjectKeyToModuleOwner(@ChildTable.@ChildColumn) like ''@OwnerOfParent''279' end, '280		)281begin282	update @ChildTable283		set @Childcolumn = null 284			from @ChildTable 285%report%					join QBMForeignKeyWrongReport fkwr on @ChildTable.@ChildColumn = fkwr.InvalidValue286			where @ChildTable.@ChildColumn > '' ''287%Mamig%		and @ChildTable.XObjectKey like ''<Key><T>@ChildTable</T><P>[a-z][0-z][0-z]-%'' 288			 and not exists ( select top 1 1289								from #TablesForOutside a with (forceseek) 290								where left(@ChildTable.@ChildColumn,  patindex(''%</T>%'', @ChildTable.@ChildColumn ) + 3 ) =  a.KeyPattern 291							)292%Mod%	and dbo.QBM_FGIModuleExists(%modcheck%) = 0293'294, case when @OwnerOfParent = '%' then '' else '295		and dbo.QBM_FCVObjectKeyToModuleOwner(@ChildTable.@ChildColumn) like ''@OwnerOfParent''296' end, '297end298'299) declare @cmdSetNullDynamicOutside nvarchar(max) = concat(N'300if exists (select top 1 1301			from @ChildTable x with (readpast)302%report%					join QBMForeignKeyWrongReport fkwr on @ChildTable.@ChildColumn = fkwr.InvalidValue303							left outer join @ParentBaseTable p on x.@ChildColumn = p.XObjectKey304				where x.@ChildColumn like ''<Key><T>@ParentTable</T><P>%''305				and p.XObjectKey is null					306				and x.@ChildColumn > '' ''307%Mamig%		and x.XObjectKey like ''<Key><T>@ChildTable</T><P>[a-z][0-z][0-z]-%'' 308				'309, case when @OwnerOfParent = '%' then '' else '310				and dbo.QBM_FCVObjectKeyToModuleOwner(x.@ChildColumn) like ''@OwnerOfParent''311' end, '312		)313begin314	update @ChildTable315		set @Childcolumn = null 316		from @ChildTable x317%report%					join QBMForeignKeyWrongReport fkwr on @ChildTable.@ChildColumn = fkwr.InvalidValue318							left outer join @ParentBaseTable p on x.@ChildColumn = p.XObjectKey319				where x.@ChildColumn like ''<Key><T>@ParentTable</T><P>%''320				and p.XObjectKey is null					321				and x.@ChildColumn > '' ''322%Mamig%		and x.XObjectKey like ''<Key><T>@ChildTable</T><P>[a-z][0-z][0-z]-%'' 323				'324, case when @OwnerOfParent = '%' then '' else '325				and dbo.QBM_FCVObjectKeyToModuleOwner(x.@ChildColumn) like ''@OwnerOfParent''326' end, '327%Mod%	and dbo.QBM_FGIModuleExists(%modcheck%) = 0328end329'330) declare @cmdDeleteDynamicOutside nvarchar(Max) = concat('331if exists (select top 1 1332			from @ChildTable x with (readpast)333%report%					join QBMForeignKeyWrongReport fkwr on @ChildTable.@ChildColumn = fkwr.InvalidValue334							left outer join @ParentBaseTable p on x.@ChildColumn = p.XObjectKey335				where x.@ChildColumn like ''<Key><T>@ParentTable</T><P>%''336				and p.XObjectKey is null					337				and x.@ChildColumn > '' ''338%Mamig%		and x.XObjectKey like ''<Key><T>@ChildTable</T><P>[a-z][0-z][0-z]-%'' 339				'340, case when @OwnerOfParent = '%' then '' else '341				and dbo.QBM_FCVObjectKeyToModuleOwner(x.@ChildColumn) like ''@OwnerOfParent''342' end, '343		)344begin345%Xori%	update @ChildTable346%Xori%		set XOrigin = 0347%Xori%		from @ChildTable x348%Xori%%report%					join QBMForeignKeyWrongReport fkwr on @ChildTable.@ChildColumn = fkwr.InvalidValue349%Xori%							left outer join @ParentBaseTable p on x.@ChildColumn = p.XObjectKey350%Xori%				where x.@ChildColumn like ''<Key><T>@ParentTable</T><P>%''351%Xori%				and p.XObjectKey is null					352%Xori%				and x.@ChildColumn > '' ''353%Mamig%		and x.XObjectKey like ''<Key><T>@ChildTable</T><P>[a-z][0-z][0-z]-%'' 354'355, case when @OwnerOfParent = '%' then '' else '356%Xori%				and dbo.QBM_FCVObjectKeyToModuleOwner(x.@ChildColumn) like ''@OwnerOfParent''357' end, '358%Xori%%Mod%	and dbo.QBM_FGIModuleExists(%modcheck%) = 0359360	delete /* Platzhalter, keine Variable */ @ChildTable361	from @ChildTable x362%report%					join QBMForeignKeyWrongReport fkwr on @ChildTable.@ChildColumn = fkwr.InvalidValue363							left outer join @ParentBaseTable p on x.@ChildColumn = p.XObjectKey364				where x.@ChildColumn like ''<Key><T>@ParentTable</T><P>%''365				and p.XObjectKey is null					366				and x.@ChildColumn > '' ''367%Mamig%		and x.XObjectKey like ''<Key><T>@ChildTable</T><P>[a-z][0-z][0-z]-%'' 368				'369, case when @OwnerOfParent = '%' then '' else '370				and dbo.QBM_FCVObjectKeyToModuleOwner(x.@ChildColumn) like ''@OwnerOfParent''371' end, '372%Mod%	and dbo.QBM_FGIModuleExists(%modcheck%) = 0373end374'375 ) select @changedTotal = 1 while @changedTotal = 1 begin select @changedTotal = 0  if @DebugSwitch > 0 begin print N'##################### neuer Durchlauf #####################'376 end if @RepairFromReport = 0 begin delete  @ElementBufferMulti  insert into @ElementBufferMulti(Ident1  , Ident2  , Ident3  , LongIdent1  , Bit1  , UID1377  , UID3  , UID2  , Int2  , Int3  ) select fr.Parenttable , fr.ChildTable , fr.ParentColumn , fr.ChildColumn , fr.IsChildPKMember , fr.ParentRestriction378 , fr.IsChildNullAble , fr.RepairMethod , fr.IsDynamicFK , sign(len(ISNULL(c.uid_dialogColumn, ''))) as HasXObjectKey from QBM_VForeignKeyRepairable fr379 join DialogTable t with (readpast) on fr.Childtable = t.TableName left outer join DialogColumn c with (readpast) on c.UID_DialogTable = t.UID_DialogTable380 and c.ColumnName = 'XObjectKey' where fr.ChildTable like @ChildTablePattern and fr.ChildColumn like @ChildColumnPattern and (fr.ParentTable like @ParentTablePattern381 or fr.Parenttable = '%') and fr.ParentColumn like @ParentColumnPattern and (@FilterByModuleGuidAllowed = 0 or @FilterByModuleGuidAllowed = 1 and fr.IsParentTabModuleGUIDAllowed382 = 1 ) and (@MasterMigMode = 0 or @MasterMigMode = 1 and exists (select top 1 1 from DialogColumn cx where cx.UID_DialogTable = t.UID_DialogTable and c.ColumnName383 = 'XObjectkey' ) ) order by fr.ChildTable,fr.ChildColumn, fr.ParentTable , fr.ParentColumn select @ElementCount = @@ROWCOUNT select @ElementIndex = @@IDENTITY384 - @ElementCount +1 select @ElementLast = @@IDENTITY  if @CheckOutsideValues = 1 begin   insert into @ElementBufferOutsideValues (ContentFull) select '385			select distinct t.tablename as tabletarget, '''386 + yt.TableName + ''', ''' + yc.ColumnName + '''387				from ' + yt.TableName + ' x join DialogTable t on x.' + yc.ColumnName + ' like ''<Key><T>'' + t.TableName + ''</T><P>%''388				'389  from DialogColumn yc with (readpast) join DialogTable yt with (readpast) on yc.UID_DialogTable = yt.UID_DialogTable and yt.TableType in ('V', 'T') and390 yt.UsageType not in ('HISTORY') left outer join DialogValidDynamicRef r with (readpast) on yc.UID_DialogColumn = r.UID_DialogColumn where yc.IsDynamicFK391 = 1 and r.UID_DialogColumn is null and yc.SchemaDataLen = 138 and yc.SchemaDataType = 'varchar' and yc.ColumnName <> 'XObjectKey'  and not exists (select392 top 1 1 from (values ( 'QER-4E75B64F5997406CAC342BAB6A46F8BE')  , ('QER-27E0BCAF583B480499300CFF790725E7')  , ('QER-9C38234735B84413BE4C612A1A7F7324')393  , ('QER-36A10DEFD23E469C8132EFB58032DD85')  , ('HDB-B6167ABE67A74F8DB42E2CBD2D50CE56')  , ('HDB-3935E716EDB64CB587D2ECA6F93ABEA9')  ) as ex (UID_DialogColumn394) where ex.UID_DialogColumn = yc.UID_DialogColumn )  and not ( yc.UID_DialogColumn = 'QBM-FAC53888D84E44CFA01B0C043CD53DE7'  and dbo.QBM_FGIDBOwner() =395 'CCC' )   select @ElementCountOutsideValues = @@ROWCOUNT select @ElementIndexOutsideValues = @@IDENTITY - @ElementCountOutsideValues +1 select @ElementLastOutsideValues396 = @@IDENTITY while @ElementIndexOutsideValues <= @ElementLastOutsideValues begin select top 1 @SQLOutsideValues = bu.ContentFull from @ElementBufferOutsideValues397 bu where bu.ElementIndex = @ElementIndexOutsideValues insert into @WerOutsideValues(Parameter1, Parameter2, Parameter3) exec sp_executesql @SQLOutsideValues398 select @ElementIndexOutsideValues += 1 end  insert into @ElementBufferMulti (Ident1  , Ident2  , Ident3  , LongIdent1  , Bit1  , UID1  , UID3  , UID2 399 , LongIdent2  , Int2  )  select tp.TableName as ParentTable , t.TableName as ChildTable ,'XObjectKey' as ParentColumn , c.ColumnName as ChildColumn , 400c.IsPKMember as IsChildPKMember , 'D' as ParentRestriction  , cc.IS_NULLABLE as IsChildNullAble , case when c.IsPKMember = 1 or cc.IS_NULLABLE = 'NO' then401 'Delete' else 'SetNull' end as RepairMethod , 'OutsideAllExistingValues' as RelationID , 4 as IsDynamicFK    from DialogColumn c with (readpast) join 402DialogTable t with (readpast) on c.UID_DialogTable = t.UID_DialogTable join @WerOutsideValues w on w.Parameter2 = t.TableName and w.Parameter3 = c.ColumnName403 join DialogTable tp with (readpast) on tp.TableName = w.Parameter1 join INFORMATION_SCHEMA.COLUMNS cc with (readpast) on cc.TABLE_NAME = t.TableName and404 cc.COLUMN_NAME = c.ColumnName where t.TableName like @ChildTablePattern and c.ColumnName like @ChildColumnPattern and (tp.TableName like @ParentTablePattern405 or tp.TableName = '%') and 'XObjectKey' like @ParentColumnPattern and (@FilterByModuleGuidAllowed = 0 or @FilterByModuleGuidAllowed = 1 and tp.IsModuleGUIDAllowed406 = 1 )  and not ( c.UID_DialogColumn = 'QBM-FAC53888D84E44CFA01B0C043CD53DE7'  and dbo.QBM_FGIDBOwner() = 'CCC' )  and (@MasterMigMode = 0 or @MasterMigMode407 = 1 and exists (select top 1 1 from DialogColumn cx where cx.UID_DialogTable = t.UID_DialogTable and c.ColumnName = 'XObjectkey' ) ) select @ElementCount408 += @@ROWCOUNT select @ElementIndex = @@IDENTITY - @ElementCount +1 select @ElementLast = @@IDENTITY end   end else  begin delete  @ElementBufferMulti 409 insert into @ElementBufferMulti(Ident1  , Ident2  , Ident3  , LongIdent1  , Bit1  , UID1  , UID3  , UID2  , Int2  , Int3  ) select fr.Parenttable , fr.ChildTable410 , fr.ParentColumn , fr.ChildColumn , fr.IsChildPKMember , fr.ParentRestriction , fr.IsChildNullAble , fr.RepairMethod     , vr.IsDynamicFK as IsDynamicFK411 , sign(len(ISNULL(c.uid_dialogColumn, ''))) as HasXObjectKey from QBMForeignKeyWrongReport fr    join QBM_VForeignKeyRepairable vr on fr.ParentTable =412 vr.Parenttable and fr.ParentColumn = vr.ParentColumn and fr.ChildTable = vr.Childtable and fr.ChildColumn = vr.ChildColumn join sys.tables tp with (readpast413) on fr.ParentTable = tp.name join sys.columns cp with (readpast) on tp.object_id = cp.object_id and cp.name = fr.ParentColumn join DialogTable t with 414(readpast) on fr.Childtable = t.TableName join DialogColumn cc with (readpast) on t.UID_DialogTable = cc.UID_DialogTable and cc.ColumnName = fr.ChildColumn415 left outer join DialogColumn c with (readpast) on c.UID_DialogTable = t.UID_DialogTable and c.ColumnName = 'XObjectKey' where fr.Parenttable > ' ' and416 (@MasterMigMode = 0 or @MasterMigMode = 1 and exists (select top 1 1 from DialogColumn cx where cx.UID_DialogTable = t.UID_DialogTable and c.ColumnName417 = 'XObjectkey' ) ) union select '%' , fr.ChildTable , fr.ParentColumn , fr.ChildColumn , fr.IsChildPKMember , fr.ParentRestriction , fr.IsChildNullAble418 , fr.RepairMethod , case fr.RelationID when 'OutsideAllTables' then 3  when 'OutsideAllExistingValues' then 2  else 0 end as IsDynamicFK , sign(len(ISNULL419(c.uid_dialogColumn, ''))) as HasXObjectKey from QBMForeignKeyWrongReport fr join DialogTable t with (readpast) on fr.Childtable = t.TableName join DialogColumn420 cc with (readpast) on t.UID_DialogTable = cc.UID_DialogTable and cc.ColumnName = fr.ChildColumn left outer join DialogColumn c with (readpast) on c.UID_DialogTable421 = t.UID_DialogTable and c.ColumnName = 'XObjectKey' where fr.RelationID in ('OutsideAllTables' , 'OutsideAllExistingValues') and (@MasterMigMode = 0 or422 @MasterMigMode = 1 and exists (select top 1 1 from DialogColumn cx where cx.UID_DialogTable = t.UID_DialogTable and c.ColumnName = 'XObjectkey' ) ) order423 by fr.ChildTable,fr.ChildColumn, fr.ParentTable , fr.ParentColumn select @ElementCount = @@ROWCOUNT select @ElementIndex = @@IDENTITY - @ElementCount 424+1 select @ElementLast = @@IDENTITY end  while @ElementIndex <= @ElementLast begin select top 1 @ParentTable = bu.Ident1  , @ChildTable = bu.Ident2  , 425@ParentColumn = bu.Ident3  , @ChildColumn = bu.LongIdent1  , @isPKMember = bu.Bit1  , @ParentRestriction = bu.UID1  , @isNullableColumn = bu.UID3  , @RepairMethod426 = bu.UID2  , @IsDynamicFK = bu.Int2  , @HasXObjectKey = bu.Int3  from @ElementBufferMulti bu where bu.ElementIndex = @ElementIndex select @ParentTableBase427 = isnull(b.TableName, @ParentTable) from DialogTable v left outer join DialogTable b on v.UID_DialogTableBase = b.UID_DialogTable where v.TableName = 428@ParentTable  if @ParentTableBase is null begin select @ParentTableBase = @ParentTable end if @RepairMethod = 'Delete' begin select @cmdwork = case @IsDynamicFK429 when 2 then @cmdDeleteDynamicOutOfValid when 3 then @cmdDeleteDynamicOutOfTables when 4 then @cmdDeleteDynamicOutside else @cmdDelete end end else begin430 select @cmdwork = case @IsDynamicFK when 2 then @cmdSetNullDynamicOutOfValid when 3 then @cmdSetNullDynamicOutOfTables when 4 then @cmdSetNullDynamicOutside431 else @cmdSetNull end end  if @IsDynamicFK > 0 begin select @cmdwork = replace(@cmdwork, '%modcheck%', @ModCheck138) end else begin if @HasXObjectKey =432 1 begin select @cmdwork = replace(@cmdwork, '%modcheck%', @ModCheck38X) end else begin select @cmdwork = replace(@cmdwork, '%modcheck%', @ModCheck38K)433 end end select @cmdwork = replace(@cmdWork, N'@ChildTable', @ChildTable) select @cmdwork = replace(@cmdwork, N'@ParentTable', @ParentTable) select @cmdwork434 = replace(@cmdwork, N'@Childcolumn', @Childcolumn) select @cmdwork = replace(@cmdwork, N'@Parentcolumn', @Parentcolumn) select @cmdwork = replace(@cmdwork435, N'%dyn%', case @IsDynamicFK when 1 then '' else '--' end ) select @cmdwork = replace(@cmdwork, N'%uid%', case @IsDynamicFK when 1 then '--' else '' end436 ) select @cmdwork = replace(@cmdwork, N'@OwnerOfParent', @OwnerOfParent) if exists (select top 1 1 from DialogTable t with (readpast) join DialogColumn437 c with (readpast) on t.UID_DialogTable = c.UID_DialogTable where t.TableName = @ChildTable and c.ColumnName = 'XOrigin' ) begin select @cmdwork = replace438(@cmdwork, '%Xori%', '') end else begin select @cmdwork = replace(@cmdwork, '%Xori%', '--') end  if @RepairOnlyNonExistingModule = 1 begin select @cmdwork439 = replace(@cmdwork, '%Mod%', '') end else begin select @cmdwork = replace(@cmdwork, '%Mod%', '--') end if @RepairFromReport = 1 begin select @cmdwork 440= replace(@cmdwork, '%report%', '') end else begin select @cmdwork = replace(@cmdwork, '%report%', '--') end select @cmdwork = replace(@cmdwork, '@ParentBaseTable'441, @ParentTableBase) if @MasterMigMode = 1  begin select @cmdwork = replace(@cmdwork, '%Mamig%', '') end else begin select @cmdwork = replace(@cmdwork, 442'%Mamig%', '--') end if @DebugSwitch > 0 begin print isnull(@cmdwork, 'hich') end select @msg = @ParentTable + '.' + @ParentColumn + ' -> ' + @ChildTable443 + '.' + @ChildColumn select @changed = 1   while @changed = 1 begin if @DebugSwitch = 2 begin print @cmdwork end exec @CountItems = QBM_PExecuteSQLWithRetry_LLP444 @SQLStatement = @cmdwork , @LockTimeout_ms = default , @MaxWaitTimeForLock_s = default , @ProcIDForJournal = @@procid , @HandleErrorSilent = 0 if @CountItems445 > 0 begin if @ParentTable = @ChildTable begin select @changed = 1 end else begin  select @ChangesForeign = 1 select @changed = 0 end select @changedTotal446 = 1 if @DebugSwitch > 0 begin print 'Change ' + @msg end end else begin select @changed = 0 if @DebugSwitch > 0 begin print 'OK     ' + @msg end end end447 select @ElementIndex += 1 end  end  END TRY BEGIN CATCH exec QBM_PSessionErrorAdd default declare @Rethrow varchar(1000) = dbo.QBM_FGISessionErrorRethrow448() RAISERROR (@Rethrow, 18, 1) WITH NOWAIT END CATCH ende: truncate table #TablesForOutside  return(@ChangesForeign) end 449